Skip to content

Instantly share code, notes, and snippets.

@phpdave
Created April 5, 2016 14:23
Show Gist options
  • Select an option

  • Save phpdave/1d6700669ba4c7a062b7f95d8b795455 to your computer and use it in GitHub Desktop.

Select an option

Save phpdave/1d6700669ba4c7a062b7f95d8b795455 to your computer and use it in GitHub Desktop.
( SELECT SUM ( FIEL ) AS "SOMEE FIEL" , SUM ( FIELD1234567890 ) AS "SOMEE AliasT
hatisbroken" , SUM ( SOMEFIELD1234 ) AS "Some alias is here123" , AVERYLONGFIELD
NAME1 FROM MYLIB . SOMETABLE12345678901234 WHERE SOMEFIELD < SOMEPARAMETER12 GRO
@robberendt
Copy link
Copy Markdown

We need to see how you're doing this. According to this:
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqprocgensql.htm?lang=en-us
I imaging you're doing something like:
CALL QSYS2.GENERATE_SQL('EMPLOYEE', 'SAMPLE_CORPDB', 'TABLE',
'GENFILE', 'DDLSOURCE', 'MASTERSRC',
CREATE_OR_REPLACE_OPTION => '1',
CONSTRAINT_OPTION => '2');
My concern is with the 3-5 parameters
DATABASE_SOURCE_FILE_NAME
DATABASE_SOURCE_FILE_LIBRARY_NAME
DATABASE_SOURCE_FILE_MEMBER
in the above example, 'GENFILE', 'DDLSOURCE', 'MASTERSRC'
If you've created that source file with something like
CRTSRCPF FILE(DDSSOURCE/GENFILE) RCDLEN(92) MBR(MASTERSRC)
it WILL break after 80 characters because the first 12 characters are used for source control (date, line number) kind of things.
So, even if you then copy it to another file you already have the breaks in it.
Understand?
It's that fixed record length concept RCDLEN(92)

@phpdave
Copy link
Copy Markdown
Author

phpdave commented Apr 7, 2016

This is how I'm calling the procedure:

CALL QSYS2.GENERATE_SQL('%', 'MYLIB', 'PROCEDURE', 
REPLACE_OPTION => '0',
CREATE_OR_REPLACE_OPTION => '1',
PRIVILEGES_OPTION=>'1',
SYSTEM_NAME_OPTION=>'1',
LABEL_OPTION=>'1',
COMMENT_OPTION=>'1'
--The output is not to a source file, but to the SQL Result set
-- ,
-- DATABASE_SOURCE_FILE_NAME=>'GENSQLSRC',
-- DATABASE_SOURCE_FILE_LIBRARY_NAME=>'MYLIB',
-- DATABASE_SOURCE_FILE_MEMBER => 'PROCEDURES'
);

Apparently the problem is the SQL result set columns are:

  1. SRCSEQ NUMERIC(6,2),
  2. SRCDAT NUMERIC(6,0),
  3. SRCDTA CHAR(80)

I want the SRCDTA to be something like VARCHAR(2000), so that if my select statement is very long and goes past the 80 characters the generated source doesn't wordwrap and break my column names and aliases.

@phpdave
Copy link
Copy Markdown
Author

phpdave commented Apr 7, 2016

SQL script to recreate the issue

--Create the schema MYLIB if you don't already have it
CREATE SCHEMA MYLIB;

--Create a stored procedure that has a line longer than 80 characters
CREATE OR REPLACE PROCEDURE MYLIB.TEST_LONG_SELECT_SP ( ) 
    DYNAMIC RESULT SETS 1 
    LANGUAGE SQL 
    SPECIFIC MYLIB.TEST_LONG_SELECT_SP 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    PROGRAM TYPE SUB 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 
        DECLARE C1 CURSOR FOR 
            SELECT 'CONSTANT1' AS A_VER_SUPER_LONG_ALIAS_NAME1 , 'CONSTANT2' AS A_VER_SUPER_LONG_ALIAS_NAME2 , 'CONSTANT3' AS A_VER_SUPER_LONG_ALIAS_NAME3 
                FROM SYSIBM . SYSDUMMY1 ; 
        OPEN C1 ; 
        RETURN ; 
    END  ; 

--Give your user profile access to it
GRANT ALTER , EXECUTE ON SPECIFIC PROCEDURE MYLIB.TEST_LONG_SELECT_SP TO MYUSER ;

--Run the Stored Proc - just to validate the stored proc is there and invokable
CALL MYLIB.TEST_LONG_SELECT_SP();

--Generate the Stored Proc
CALL QSYS2.GENERATE_SQL('TEST_LONG_SELECT_SP', 'MYLIB', 'PROCEDURE', 
    REPLACE_OPTION => '0',
    CREATE_OR_REPLACE_OPTION => '1',
    PRIVILEGES_OPTION=>'1',
    SYSTEM_NAME_OPTION=>'1',
    LABEL_OPTION=>'1',
    COMMENT_OPTION=>'1'
    --The output is not to a source file, but to the SQL Result set
    -- ,
    -- DATABASE_SOURCE_FILE_NAME=>'GENSQLSRC',
    -- DATABASE_SOURCE_FILE_LIBRARY_NAME=>'MYLIB',
    -- DATABASE_SOURCE_FILE_MEMBER => 'PROCEDURES'
);

Result: (note: SRCSEQ 27 and 28 )

1.00    160407  --  Generate SQL 

2.00    160407  --  Version:                    V7R1M0 100423 

3.00    160407  --  Generated on:               04/07/16 11:44:39 

4.00    160407  --  Relational Database:        MYIBMI 

5.00    160407  --  Standards Option:           DB2 for i 

6.00    160407    

7.00    160407  SET PATH "QSYS","QSYS2","SYSPROC" ; 

8.00    160407    

9.00    160407  CREATE OR REPLACE PROCEDURE MYLIB.TEST_LONG_SELECT_SP ( ) 

10.00   160407      DYNAMIC RESULT SETS 1 

11.00   160407      LANGUAGE SQL 

12.00   160407      SPECIFIC MYLIB.TEST_LONG_SELECT_SP 

13.00   160407      NOT DETERMINISTIC 

14.00   160407      READS SQL DATA 

15.00   160407      CALLED ON NULL INPUT 

16.00   160407      PROGRAM TYPE SUB 

17.00   160407      SET OPTION  ALWBLK = *ALLREAD , 

18.00   160407      ALWCPYDTA = *OPTIMIZE , 

19.00   160407      COMMIT = *NONE , 

20.00   160407      DECRESULT = (31, 31, 00) , 

21.00   160407      DFTRDBCOL = *NONE , 

22.00   160407      DYNDFTCOL = *NO , 

23.00   160407      DYNUSRPRF = *USER , 

24.00   160407      SRTSEQ = *HEX   

25.00   160407      BEGIN 

26.00   160407  DECLARE C1 CURSOR FOR 

27.00   160407  SELECT 'CONSTANT1' AS A_VER_SUPER_LONG_ALIAS_NAME1 , 'CONSTANT2' AS A_VER_SUPER_    
28.00   160407  LONG_ALIAS_NAME2 , 'CONSTANT3' AS A_VER_SUPER_LONG_ALIAS_NAME3 

29.00   160407  FROM SYSIBM . SYSDUMMY1 ; 

30.00   160407  OPEN C1 ; 

31.00   160407  RETURN ; 

32.00   160407  END  ; 

33.00   160407    

34.00   160407  GRANT ALTER , EXECUTE   

35.00   160407  ON SPECIFIC PROCEDURE MYLIB.TEST_LONG_SELECT_SP 

36.00   160407  TO MYUSER ; 

37.00   160407    

@phpdave
Copy link
Copy Markdown
Author

phpdave commented Apr 7, 2016

showingoutputwithspecialsymbols

@robberendt
Copy link
Copy Markdown

Didn't know it would return it into the result set. "Undocumented feature". :-)
I wonder how threw in all the blank lines, or to break a line before the "FROM". I agree that breaking before the FROM is good formatting, but how they did that.
You may end up concatenating your result set.

@robberendt
Copy link
Copy Markdown

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment