I always place the code I use to create DDL tables, indexes, views, etc. in a source member. It has always frustrated me that the code could not go beyond the 80th column, without the compiling failing. As part of IBM i 7.5 and 7.4 TR6 a new special value is allowed in the Run SQL Statement command, RUNSQLSTM.
In the RUNSQLSTM command there is a parameter for the source margins, MARGINS, that allows me to give the position of the right margin of the code within the member. The default is 80, but now there is a keyword I can use in its place: *SRCFILE. This "tells" the compiler to use the entire length of the source member, rather than the first 80 characters only.
If I made a SQL statement that is greater than 80 characters in a source member, like this:
...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REPLACE DELETE ROWS ; |
And I try to compile the source member with the default values of RUNSQLSTM I get an error:
RUNSQLSTM or RUNSQL command failed. |
If I look in the compile listing I see that the compiler cropped the statement at the 80th position:
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
1 CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REP
|
If I change the value of the margins keyword to *SRCFILE:
Run SQL Statements (RUNSQLSTM)
Type choices, press Enter.
Source file . . . . . . . . . . SRCFILE DEVSRC
Library . . . . . . . . . . . MYLIB
Source member . . . . . . . . . SRCMBR SQLTABLE
Source stream file . . . . . . . SRCSTMF
Commitment control . . . . . . . COMMIT *NONE
Naming . . . . . . . . . . . . . NAMING *SYS
Additional Parameters
Severity level . . . . . . . . . ERRLVL 20
Source margins: MARGINS
Right margin . . . . . . . . . *SRCFILE
|
The compile is successful. And when I look at the compile listing I can see that compiler handles the entire source member's line:
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
1 CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REP
1 LACE DELETE ROWS ;
|
You may not consider this to be a big deal, but it will save me a lot of time reformatting statements I have copied from ACS's Run SQL Scripts into a source member.
This article was written for IBM i 7.5 and 7.4 TR6.
Interesting trick but, if you write the sql in ACS Run SQL Scripts and you formatted it you obtain this :
ReplyDeleteCREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (
C1 VARCHAR(30),
C2 VARCHAR(30)
)
ON REPLACE DELETE ROWS;
that is more readeable the 1 line.
Don't you think ?
I agree what you give is more readable, and is how I normally write the CREATE TABLE statement. I needed to have a statement that is more than 80 characters to illustrate this improved feature.
DeleteNice. Just put this to good use.
ReplyDelete