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. -Ted
Hey, Ted: 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. –Lynne
Hey, Ted: 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 Jan is correct, of course. I appreciate his taking the time to point out the error. –Ted
Hey, Ted: 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? –Harold 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. –Ted
Hey, Ted: 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). –Daz 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. –Ted RELATED STORIES How To Rename Your Local Database Avoid an Unnecessary CPYF Error V6R1 Run SQL Scripts: Print and Save Result Sets The SPACE Function Takes Up a Lot of Space
|
