Odds and Ends: The Reader is the Guru
March 2, 2011 Dear Professional
The emails I get from you provide a sterling illustration that the readers of this august publication are the true gurus in the world of IBM i. Here are a few contributions that I hope may prove useful to some of you.
I’d like to add an important point when renaming the local database. IBM’s own jobs rely on this local database name entry existing. They don’t care if you have deleted it. They will recreate it pretty quickly if it does not have a name entered, so the commands to delete the old and add the new name may have to be run quickly. It is a good idea to pre-type them in a session or put them in a script to get them to run fast enough.
Regarding your article, Avoid an Unnecessary CPYF Error, I think you could have formulated the MONMSG better.
Your test of message CPF2817 contains an invalid assumption.
CPYF FROMFILE(AAA) TOFILE(BBB) MBROPT(*REPLACE) MONMSG MSGID(CPF2817) EXEC(CLRPFM BBB) /* AAA IS EMPTY */
CPF2817 can have other meanings besides that the “from” file is empty. For example:
CPYF FROMFILE(unexisting) TOFILE(myFile) MBROPT(*REPLACE) From-file UNEXISTING in *LIBL not found. Copy command ended because of error.
Better would be:
CPYF FROMFILE(AAA) TOFILE(BBB) MBROPT(*REPLACE) MONMSG MSGID(CPF2817) CMPDTA(CPF2869) EXEC(CLRPFM BBB) /* AAA IS EMPTY */
Jan is correct, of course. I appreciate his taking the time to point out the error.
In his article V6R1 Run SQL Scripts: Print and Save Result Sets, Skip Marchesani wrote about saving the results of an SQL query to Excel and printing. How can I save the results of an SQL command as a new DB2 file?
In interactive, green-screen SQL (STRSQL), press F13, then option 1, to get the session attributes screen, where you can choose to output to a file.
For batch, run CREATE TABLE xxx/yyyy AS (SELECT ….) within RUNSQLSTM.
You might find this SQL function of interest.
Along similar lines to your article, it deals with right-aligning a piece of text in a given space (up to 1K).
Here’s Daz’s function:
Create Function RAlign (InpStr varchar(1024), InpPos integer) RETURNS varchar(1024) LANGUAGE SQL DETERMINISTIC RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION SET OPTION OUTPUT=*PRINT, DBGVIEW=*SOURCE BEGIN DECLARE TmpTxt varchar(1024); DECLARE InpLen integer default 0; SET InpLen = length(rtrim(InpStr)); IF InpLen <= InpPos THEN SET TmpTxt = substr(char(' ', 1024),1,InpPos-InpLen) || rtrim(InpStr); ELSE SET TmpTxt = (substr(InpStr,InpLen - InpPos + 1 , InpPos)); END IF; RETURN TmpTxt; END;
I offer RAlign as an alternative to my RADJ function, in case you may prefer it.