Created
April 5, 2016 14:23
-
-
Save phpdave/1d6700669ba4c7a062b7f95d8b795455 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ( 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 |
Author
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:
- SRCSEQ NUMERIC(6,2),
- SRCDAT NUMERIC(6,0),
- 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.
Author
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
Author
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.
There's also this option:
https://www-912.ibm.com/r_dir/ReqDesChange.nsf/Request_for_Design_Change?OpenForm
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment

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)