[DB2 z/OS] SQL writing code / methods

Philip Sevetson

[DB2 z/OS] SQL writing code / methods
It happens that I needed to pull a bunch of tablespace copy records last week in order to execute UNLOAD TABLESPACE / FROMCOPY, pointing to image copies from a couple of days previous. What I did at the time was a series of Common Table Expressions (CTEs), each building on the last, to create a multi-line UNLOAD statement for each table in scope. See bottom for the code

Now, doing so for the first time was a right royal pain, when at each step when something would fail, and I'd have to go back or even start over. However, it looks to me like the last two Selects (one CTE and the final SELECT) are fully reusable (except for possibly varying number of lines written per command), so maybe that's a one-time problem.

My question to the group is: for those of you, if any, who use SQL to build things like DDL and Utility statements and other code, do you find it easier to use a succession of CTEs? Or do you use Declared Global Temporary Tables and do the same thing with multiple SELECT statements? Is that easier, in the long run, despite having to declare-and-populate in two complete statements for each stage?

I ask because, while this will run as a single statement, it looks to me like debugging would be easier if using the DGTTs.

Comments?

... my example statement, complete except for proprietary information:

WITH COPYDATA(DBNAME, TSNAME, DSNAME)
AS (SELECT
DBNAME
,TSNAME
,DSNAME
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND ICBACKUP = ' '
AND DBNAME IN ([proprietary information])
AND DATE(TIMESTAMP) = '2017-11-28'
AND TIME(TIMESTAMP) > '21.40'
),
RAW_UTIL_STMT (LINE1, LINE2, LINE3, STMT_NBR)
AS (SELECT 'UNLOAD TABLESPACE ' CONCAT RTRIM(DBNAME) CONCAT '.'
CONCAT RTRIM(TSNAME) AS LINE1,
'FROMCOPY ' CONCAT DSNAME AS LINE2,
'UNLDDN(TMPLSYSR) PUNCHDDN(TMPLSYSP)' AS LINE3,
RTRIM(DBNAME) CONCAT '.' CONCAT TSNAME AS STMT_NBR
FROM COPYDATA
),
PAGINATED_UTIL_STMT(DDL_TEXT, LINE_WITHIN_STMT, STMT_NBR)
AS (SELECT LINE1, 1, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE2, 2, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE3, 3, STMT_NBR
FROM RAW_UTIL_STMT
)
SELECT CHAR(DDL_TEXT,80)
FROM PAGINATED_UTIL_STMT
ORDER BY STMT_NBR, LINE_WITHIN_STMT
;

Notes:
1. I run this using DSNTIAUL, which will format the output as FB/80 because of the format of the actual (final) SELECT. The output then becomes usable as SYSIN for DSNUTILB/DSNUPROC.
2. The number of lines of output is determined by the third query.
3. The order columns (STMT_NBR, LINE_WITHIN_STMT) are mandatory, or the output is potentially disordered.

--Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bill Gallagher

[DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)
For me, I would do neither. I'd create a DB2 SQL stored procedure to dynamically build the utility statements and then execute them.

Here's a snippet of one that I did at my previous job (it executed RUNSTATS based on criteria derived from RTS):

OPEN c6;

SET v_EOC = 'N';

FETCH FROM c6 INTO v_ObjType, v_ObjQual, v_ObjName;

WHILE (v_EOC = 'N') DO

IF v_ObjType = 'TS' THEN
SET v_UtilStmt = ' RUNSTATS TABLESPACE ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' TABLE ALL INDEX ' ||
' (ALL KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20) SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
ELSE
SET v_UtilStmt = ' RUNSTATS INDEX ( ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' ) ' ||
' KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20 SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
END IF;

-- Terminate the utility (in case there is an old one still out there)

CALL SYSPROC.ADMIN_COMMAND_DB2 (v_DB2Cmd, v_CmdLen,
v_procType, NULL, v_CmdsExec, v_IFI_RetCd,
v_IFI_ReasCd, v_ExcBytes, v_GIFI_ReasCd,
v_GIFI_ExcBytes, v_RetCode, v_Message);

-- Here's where RUNSTATS is executed

CALL SYSPROC.DSNUTILU (v_UtilID, v_Restart, v_UtilStmt,
v_RetCode);

ASSOCIATE RESULT SET LOCATOR (v_Rs1)
WITH PROCEDURE SYSPROC.DSNUTILU;

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:17 PM
To: [login to unmask email]
Subject: [DB2-L] - [DB2 z/OS] SQL writing code / methods

It happens that I needed to pull a bunch of tablespace copy records last week in order to execute UNLOAD TABLESPACE / FROMCOPY, pointing to image copies from a couple of days previous. What I did at the time was a series of Common Table Expressions (CTEs), each building on the last, to create a multi-line UNLOAD statement for each table in scope. See bottom for the code

Now, doing so for the first time was a right royal pain, when at each step when something would fail, and I'd have to go back or even start over. However, it looks to me like the last two Selects (one CTE and the final SELECT) are fully reusable (except for possibly varying number of lines written per command), so maybe that's a one-time problem.

My question to the group is: for those of you, if any, who use SQL to build things like DDL and Utility statements and other code, do you find it easier to use a succession of CTEs? Or do you use Declared Global Temporary Tables and do the same thing with multiple SELECT statements? Is that easier, in the long run, despite having to declare-and-populate in two complete statements for each stage?

I ask because, while this will run as a single statement, it looks to me like debugging would be easier if using the DGTTs.

Comments?

... my example statement, complete except for proprietary information:

WITH COPYDATA(DBNAME, TSNAME, DSNAME)
AS (SELECT
DBNAME
,TSNAME
,DSNAME
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND ICBACKUP = ' '
AND DBNAME IN ([proprietary information])
AND DATE(TIMESTAMP) = '2017-11-28'
AND TIME(TIMESTAMP) > '21.40'
),
RAW_UTIL_STMT (LINE1, LINE2, LINE3, STMT_NBR)
AS (SELECT 'UNLOAD TABLESPACE ' CONCAT RTRIM(DBNAME) CONCAT '.'
CONCAT RTRIM(TSNAME) AS LINE1,
'FROMCOPY ' CONCAT DSNAME AS LINE2,
'UNLDDN(TMPLSYSR) PUNCHDDN(TMPLSYSP)' AS LINE3,
RTRIM(DBNAME) CONCAT '.' CONCAT TSNAME AS STMT_NBR
FROM COPYDATA
),
PAGINATED_UTIL_STMT(DDL_TEXT, LINE_WITHIN_STMT, STMT_NBR)
AS (SELECT LINE1, 1, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE2, 2, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE3, 3, STMT_NBR
FROM RAW_UTIL_STMT
)
SELECT CHAR(DDL_TEXT,80)
FROM PAGINATED_UTIL_STMT
ORDER BY STMT_NBR, LINE_WITHIN_STMT
;

Notes:
1. I run this using DSNTIAUL, which will format the output as FB/80 because of the format of the actual (final) SELECT. The output then becomes usable as SYSIN for DSNUTILB/DSNUPROC.
2. The number of lines of output is determined by the third query.
3. The order columns (STMT_NBR, LINE_WITHIN_STMT) are mandatory, or the output is potentially disordered.

--Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Philip Sevetson

[DB2 z/OS] SQL writing code / methods
(in response to Bill Gallagher)
Bill,


1) This is interesting.

2) Technical query: Do you have to have a shell program (REXX or another PL) to execute this? I'm not used to using INTO except in a programming language, and either TEP2 or SPUFI (I forget which) doesn't like stored procedures as of a couple of releases ago.

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:39 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

For me, I would do neither. I'd create a DB2 SQL stored procedure to dynamically build the utility statements and then execute them.

Here's a snippet of one that I did at my previous job (it executed RUNSTATS based on criteria derived from RTS):

OPEN c6;

SET v_EOC = 'N';

FETCH FROM c6 INTO v_ObjType, v_ObjQual, v_ObjName;

WHILE (v_EOC = 'N') DO

IF v_ObjType = 'TS' THEN
SET v_UtilStmt = ' RUNSTATS TABLESPACE ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' TABLE ALL INDEX ' ||
' (ALL KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20) SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
ELSE
SET v_UtilStmt = ' RUNSTATS INDEX ( ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' ) ' ||
' KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20 SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
END IF;

-- Terminate the utility (in case there is an old one still out there)

CALL SYSPROC.ADMIN_COMMAND_DB2 (v_DB2Cmd, v_CmdLen,
v_procType, NULL, v_CmdsExec, v_IFI_RetCd,
v_IFI_ReasCd, v_ExcBytes, v_GIFI_ReasCd,
v_GIFI_ExcBytes, v_RetCode, v_Message);

-- Here's where RUNSTATS is executed

CALL SYSPROC.DSNUTILU (v_UtilID, v_Restart, v_UtilStmt,
v_RetCode);

ASSOCIATE RESULT SET LOCATOR (v_Rs1)
WITH PROCEDURE SYSPROC.DSNUTILU;

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:17 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 z/OS] SQL writing code / methods

It happens that I needed to pull a bunch of tablespace copy records last week in order to execute UNLOAD TABLESPACE / FROMCOPY, pointing to image copies from a couple of days previous. What I did at the time was a series of Common Table Expressions (CTEs), each building on the last, to create a multi-line UNLOAD statement for each table in scope. See bottom for the code

Now, doing so for the first time was a right royal pain, when at each step when something would fail, and I'd have to go back or even start over. However, it looks to me like the last two Selects (one CTE and the final SELECT) are fully reusable (except for possibly varying number of lines written per command), so maybe that's a one-time problem.

My question to the group is: for those of you, if any, who use SQL to build things like DDL and Utility statements and other code, do you find it easier to use a succession of CTEs? Or do you use Declared Global Temporary Tables and do the same thing with multiple SELECT statements? Is that easier, in the long run, despite having to declare-and-populate in two complete statements for each stage?

I ask because, while this will run as a single statement, it looks to me like debugging would be easier if using the DGTTs.

Comments?

... my example statement, complete except for proprietary information:

WITH COPYDATA(DBNAME, TSNAME, DSNAME)
AS (SELECT
DBNAME
,TSNAME
,DSNAME
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND ICBACKUP = ' '
AND DBNAME IN ([proprietary information])
AND DATE(TIMESTAMP) = '2017-11-28'
AND TIME(TIMESTAMP) > '21.40'
),
RAW_UTIL_STMT (LINE1, LINE2, LINE3, STMT_NBR)
AS (SELECT 'UNLOAD TABLESPACE ' CONCAT RTRIM(DBNAME) CONCAT '.'
CONCAT RTRIM(TSNAME) AS LINE1,
'FROMCOPY ' CONCAT DSNAME AS LINE2,
'UNLDDN(TMPLSYSR) PUNCHDDN(TMPLSYSP)' AS LINE3,
RTRIM(DBNAME) CONCAT '.' CONCAT TSNAME AS STMT_NBR
FROM COPYDATA
),
PAGINATED_UTIL_STMT(DDL_TEXT, LINE_WITHIN_STMT, STMT_NBR)
AS (SELECT LINE1, 1, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE2, 2, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE3, 3, STMT_NBR
FROM RAW_UTIL_STMT
)
SELECT CHAR(DDL_TEXT,80)
FROM PAGINATED_UTIL_STMT
ORDER BY STMT_NBR, LINE_WITHIN_STMT
;

Notes:
1. I run this using DSNTIAUL, which will format the output as FB/80 because of the format of the actual (final) SELECT. The output then becomes usable as SYSIN for DSNUTILB/DSNUPROC.
2. The number of lines of output is determined by the third query.
3. The order columns (STMT_NBR, LINE_WITHIN_STMT) are mandatory, or the output is potentially disordered.

--Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bill Gallagher

[DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)
Phil,

Yes, I implemented this as a batch job that called a REXX which then called the stored procedure.

But you can also invoke the stored procedure from Data Studio, or by issuing a call to the SP from a DB2 command editor window if you have a DB2 client (such as DB2 Connect) installed on your PC/laptop/workstation.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:48 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Bill,


1) This is interesting.

2) Technical query: Do you have to have a shell program (REXX or another PL) to execute this? I'm not used to using INTO except in a programming language, and either TEP2 or SPUFI (I forget which) doesn't like stored procedures as of a couple of releases ago.

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:39 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

For me, I would do neither. I'd create a DB2 SQL stored procedure to dynamically build the utility statements and then execute them.

Here's a snippet of one that I did at my previous job (it executed RUNSTATS based on criteria derived from RTS):

OPEN c6;

SET v_EOC = 'N';

FETCH FROM c6 INTO v_ObjType, v_ObjQual, v_ObjName;

WHILE (v_EOC = 'N') DO

IF v_ObjType = 'TS' THEN
SET v_UtilStmt = ' RUNSTATS TABLESPACE ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' TABLE ALL INDEX ' ||
' (ALL KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20) SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
ELSE
SET v_UtilStmt = ' RUNSTATS INDEX ( ' ||
RTRIM(v_ObjQual) ||
'.' || v_ObjName || ' ) ' ||
' KEYCARD FREQVAL NUMCOLS 1 ' ||
' COUNT 20 SHRLEVEL CHANGE ' ||
' REPORT YES UPDATE ALL';
END IF;

-- Terminate the utility (in case there is an old one still out there)

CALL SYSPROC.ADMIN_COMMAND_DB2 (v_DB2Cmd, v_CmdLen,
v_procType, NULL, v_CmdsExec, v_IFI_RetCd,
v_IFI_ReasCd, v_ExcBytes, v_GIFI_ReasCd,
v_GIFI_ExcBytes, v_RetCode, v_Message);

-- Here's where RUNSTATS is executed

CALL SYSPROC.DSNUTILU (v_UtilID, v_Restart, v_UtilStmt,
v_RetCode);

ASSOCIATE RESULT SET LOCATOR (v_Rs1)
WITH PROCEDURE SYSPROC.DSNUTILU;

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, December 04, 2017 12:17 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 z/OS] SQL writing code / methods

It happens that I needed to pull a bunch of tablespace copy records last week in order to execute UNLOAD TABLESPACE / FROMCOPY, pointing to image copies from a couple of days previous. What I did at the time was a series of Common Table Expressions (CTEs), each building on the last, to create a multi-line UNLOAD statement for each table in scope. See bottom for the code

Now, doing so for the first time was a right royal pain, when at each step when something would fail, and I'd have to go back or even start over. However, it looks to me like the last two Selects (one CTE and the final SELECT) are fully reusable (except for possibly varying number of lines written per command), so maybe that's a one-time problem.

My question to the group is: for those of you, if any, who use SQL to build things like DDL and Utility statements and other code, do you find it easier to use a succession of CTEs? Or do you use Declared Global Temporary Tables and do the same thing with multiple SELECT statements? Is that easier, in the long run, despite having to declare-and-populate in two complete statements for each stage?

I ask because, while this will run as a single statement, it looks to me like debugging would be easier if using the DGTTs.

Comments?

... my example statement, complete except for proprietary information:

WITH COPYDATA(DBNAME, TSNAME, DSNAME)
AS (SELECT
DBNAME
,TSNAME
,DSNAME
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND ICBACKUP = ' '
AND DBNAME IN ([proprietary information])
AND DATE(TIMESTAMP) = '2017-11-28'
AND TIME(TIMESTAMP) > '21.40'
),
RAW_UTIL_STMT (LINE1, LINE2, LINE3, STMT_NBR)
AS (SELECT 'UNLOAD TABLESPACE ' CONCAT RTRIM(DBNAME) CONCAT '.'
CONCAT RTRIM(TSNAME) AS LINE1,
'FROMCOPY ' CONCAT DSNAME AS LINE2,
'UNLDDN(TMPLSYSR) PUNCHDDN(TMPLSYSP)' AS LINE3,
RTRIM(DBNAME) CONCAT '.' CONCAT TSNAME AS STMT_NBR
FROM COPYDATA
),
PAGINATED_UTIL_STMT(DDL_TEXT, LINE_WITHIN_STMT, STMT_NBR)
AS (SELECT LINE1, 1, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE2, 2, STMT_NBR
FROM RAW_UTIL_STMT
UNION
SELECT LINE3, 3, STMT_NBR
FROM RAW_UTIL_STMT
)
SELECT CHAR(DDL_TEXT,80)
FROM PAGINATED_UTIL_STMT
ORDER BY STMT_NBR, LINE_WITHIN_STMT
;

Notes:
1. I run this using DSNTIAUL, which will format the output as FB/80 because of the format of the actual (final) SELECT. The output then becomes usable as SYSIN for DSNUTILB/DSNUPROC.
2. The number of lines of output is determined by the third query.
3. The order columns (STMT_NBR, LINE_WITHIN_STMT) are mandatory, or the output is potentially disordered.

--Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Jack Campbell

RE: [DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)

Phil,

I'm very much like Bill, for simple work I would use a single SQL stmt (run under DSNTIAUL) - If I need more than 1 SQL stmt due to 72 char limit , I would incorporate a UNION ALL and a sequence number to keep things in order.

For more complex SQL I prefer to write a Stored Proc and call this using a simple REXX routine. I have the Stored Proc write all SQL to a predefined "Utility stmt table" and then simply unload the results with an order by. This means I get all the benefits of PL/SQL validation/formatting/logic path controls.

simple SQL example:

SELECT SUBSTR(T1.TEXT,1,80)                                                    
 FROM  (SELECT DISTINCT PKG.COLLID, PKG.NAME, PKG.VERSION,                     
        '1' AS SEQ,                                                            
        'REBIND ' ||                                                           
           CASE                                                                
             WHEN TYPE = 'T' THEN 'TRIGGER '                                   
             ELSE ' '                                                          
        END                                                                    
        || 'PACKAGE ('||RTRIM(DCOLLID)||'.'||RTRIM(DNAME)||                    
        CASE PKG.VERSION                                                       
        WHEN '' THEN ') -'                                                     
        ELSE '.(' ||RTRIM(PKG.VERSION) ||')) -'                                
        END                                                                    
        AS TEXT                                                                
   FROM SYSIBM.SYSPACKDEP PKD,                                                 
        SYSIBM.SYSPACKAGE PKG                                                  
  WHERE                                                                        
   PKG.COLLID   = PKD.DCOLLID   AND                                            
   PKG.LOCATION = PKD.DLOCATION AND                                            
   PKG.NAME     = PKD.DNAME     AND                                            
   PKG.CONTOKEN = PKD.DCONTOKEN AND                                            
   PKG.OWNER    = PKD.DOWNER    AND                                            
   ( other predicates to filter on)                                                                           
UNION ALL                                                                      
       (SELECT DISTINCT PKG.COLLID, PKG.NAME, PKG.VERSION,                     
        '2' AS SEQ,                                                            
        '  PLANMGMT(EXTENDED) APCOMPARE(WARN);' AS TEXT                        
   FROM SYSIBM.SYSPACKDEP PKD,                                                 
        SYSIBM.SYSPACKAGE PKG                                                  
   WHERE                                                                       
   PKG.COLLID   = PKD.DCOLLID   AND                                            
   PKG.LOCATION = PKD.DLOCATION AND                                            
   PKG.NAME     = PKD.DNAME     AND                                            
   PKG.CONTOKEN = PKD.DCONTOKEN AND                                            
   PKG.OWNER    = PKD.DOWNER    AND                                             

   ( other predicates to filter on)                                                                           
       ) ) AS T1                                                               
ORDER BY T1.COLLID, T1.NAME, T1.VERSION, SEQ                                   
WITH UR;                      

 

HTH

 

Jack                  

J&#248;rn Thyssen

RE: [DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)

Hi Phil,

FWIW, CLP available for Db2 for z/OS as well: http://www.idug.org/p/bl/et/blogid=143&blogaid=320

It supports CALL.

For batch you can start it using BPXBATCH.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Philip Sevetson

[DB2 z/OS] SQL writing code / methods
(in response to Jørn Thyssen)
::BOGGLE::

That’s a pretty big door you’re showing me, there. Lots of things we could do with that. Thanks.

From: Jørn Thyssen [mailto:[login to unmask email]
Sent: Tuesday, December 05, 2017 4:51 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods


Hi Phil,

FWIW, CLP available for Db2 for z/OS as well: http://www.idug.org/p/bl/et/blogid=143&blogaid=320

It supports CALL.

For batch you can start it using BPXBATCH.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

Views are personal.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)

Phil,

I am an SQL geek, so I build large and complex SQLs to generate any commands or reports that I need. I am absolutely fine with your technique of reusable building blocks inside your query as Common Table Expressions. That is precisely what I do to limit levels of nested table subqueries. Bear in mind that each use of a CTE can cause DB2 Optimizer to merge the definition of the CTE into the reference point. To avoid recalculating the results of a CTE used in more than one place, I may choose to materialise it in a work file. Your PLAN_TABLE explained access paths with tell you whether a CTE is materialised or not in most cases. Look for DSNWFxxx for example. I often cause my CTEs to be materialised using DISTINCT or GROUP BY.

We are usually talking about processing low volume data so absolute top performance of the SQL Queries is not paramount. Satisfactory performance is usually good enough.

Where my sub-select needs to produce multiple lines of output, I join to a dummy table containing integer numbers 1 to 100 to become line sequencing numbers, and use CASE to select what fields are used (concatenated) on each result line. I also keep what fields are needed to sort the output lines into correct sequence.

I very much understand your difficulty, when enhancing your trial query, that you run into an error, and DB2 is not telling you which CTE contains the error. Sometimes error message is a little vague. I recommend you to code a very basic debugging SELECT after every single CTE, used to test if that CTE is producing results successfully. When that debug SELECT is not required, don't remove it. Just comment it out in case is needed for debugging again later. e.g.

-- SELECT * FROM CTE_NAME ORDER BY ...
-- FETCH FIRST 100 ROWS ONLY WITH UR; 

Sometimes an error in a large CTE subselect is still hard to track down. I have been known to resort to removal or commenting out large sections of a CTE temporarily till I can see a change in the error message which means I have taken out the first encountered problem. In worst situation I did a binary search for the error using this method.

Many times SQL errors do report some useful text at point of the error that you can search for. Unfortunately not always the case. Select Columns mismatched by a GROUP BY is a common error to resolve. SUBSTR has invalid arguments or value out of range of a numeric column are other common problems, so I learn to be very careful to avoid problems hard to track down (in very large SQL queries).

In an earlier version of DB2 for zOS I was limited to 32K queries and RDS could only handle certain complexity. These restrictions are now gone. I only run into RDS problems (Error with no apparent cause) occasionally and have always found a way around them.

Michael Hannan (SQL Crazy Person)

DB2 Application Performance Specialist
CPT Global Ltd

P.S. I like to build my queries so that they produce nice text output using DSNTIAUL, but also run and test them using SPUFI or any other mechanism. To be suited to DSNTIAUL, all output columns need to have Null Indicators removed, and Numerics converted to CHAR, and all VARCHAR converted to CHAR to remove 2 byte length prefixes. SUBSTR and CHAR functions return fixed length CHAR columns.

Edited By:
Michael Hannan[Organization Members] @ Dec 10, 2017 - 05:32 AM (Europe/Berlin)

Patrick Bossman

RE: [DB2 z/OS] SQL writing code / methods
(in response to Michael Hannan)

Another option is to add RAND() to materialize.

Patrick Bossman

RE: [DB2 z/OS] SQL writing code / methods
(in response to Michael Hannan)

Regarding commenting large chunks of queries out, I'll extend on Michael's suggestion (which I also use) and credit Bill Schray, but I always put comma's at the front instead of the back to make commenting sections out during debugging easier.

SELECT C1

              , C2

--              , C3

FROM T1

--           , T2

WHERE ...

Philip Sevetson

[DB2 z/OS] SQL writing code / methods
(in response to Michael Hannan)
Michael,

Thanks very much. I appreciate the validation of the basic method.

Commenting out the intermediate SELECTs is fine for me, most of the time. However, since I use DSNTIAUL to write out the final result to a sequential file, I have to remove comments at production execution time. Irritating.

I’m thinking about whether I want to create an artifice on the side to deal with this, or something like that – a final permanent table (four columns; jobname char(8), generated_timestamp timestamp, statement_nbr int, statement_text char 80; unique index on the first three) to which I could write results. Then I’d write them out in a separate DSNTIAUL step, and run them in a third step. It’s a lot more elaborate, but gives full flexibility in the creation of the SQL which actually derives the statements (meaning that I could leave comments in!!).

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Saturday, December 09, 2017 11:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods


Phil,

I am an SQL geek, so I build large and complex SQLs to generate any commands or reports that I need. I am absolutely fine with your technique of reusable building blocks inside your query as Common Table Expressions. That is precisely what I do to limit levels of nested table subqueries. Bear in mind that each use of a CTE can cause DB2 Optimizer to merge the definition of the CTE into the reference point. To avoid recalculating the results of a CTE used in more than one place, I may choose to materialise it in a work file. Your PLAN_TABLE explained access paths with tell you whether a CTE is materialised or not in most cases. Look for DSNWFxxx for example. I often cause my CTEs to be materialised using DISTINCT or GROUP BY.

We are usually talking about processing low volume data so absolute top performance of the SQL Queries is not paramount. Satisfactory performance is usually good enough.

Where my sub-select needs to produce multiple lines of output, I join to a dummy table containing integer numbers 1 to 100 to become line sequencing numbers, and use CASE to select what fields are used (concatenated) on each result line. I also keep what fields are needed to sort the output lines into correct sequence.

I very much understand your difficulty, when enhancing your trial query, that you run into an error, and DB2 is not telling you which CTE contains the error. Sometimes error message is a little vague. I recommend you to code a very basic debugging SELECT after every single CTE, used to test if that CTE is producing results successfully. When that debug SELECT is not required, don't remove it. Just comment it out in case is needed for debugging again later. e.g.

-- SELECT * FROM CTE_NAME ORDER BY ...
-- FETCH FIRST 100 ROWS ONLY WITH UR;

Sometimes an error in a large CTE subselect is still hard to track down. I have been known to resort to removal or commenting out large sections of a CTE temporarily till I can see a change in the error message which means I have taken out the first encountered problem. In worst situation I did a binary search for the error using this method.

Many times SQL errors do report some useful text at point of the error that you can search for. Unfortunately not always the case. Select Columns mismatched by a GROUP BY is a common error to resolve. SUBSTR has invalid arguments or value out of range of a numeric column are other common problems, so I learn to be very careful to avoid problems hard to track down (in very large SQL queries).

In an earlier version of DB2 for zOS I was limited to 32K queries and RDS could only handle certain complexity. These restrictions are now gone. I only run into RDS problems (Error with no apparent cause) occasionally and have always found a way around them.

Michael Hannan (SQL Crazy Person)

DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)

Phil,

Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step. My SQLs are far too complex, to be devoid of comments. Without comments I would not understand what my own SQL was doing. LOL. A maintenance nightmare.

//*---------------------------------------------------------------*/
//* REMOVE -- COMMENTS FROM SQL TEMPLATES
//*---------------------------------------------------------------*/
//I1 EXEC PGM=ICETOOL
//TOOLIN DD *
COPY FROM(IN1) TO(OUT1) USING(SEL1)
//SEL1CNTL DD *
OMIT COND=(1,2,CH,EQ,C'--')
//IN1 DD DISP=SHR,DSN=&SOURCLIB(&SAVEEXP)
//*OUT1 DD SYSOUT=* */
//OUT1 DD DISP=(NEW,PASS),DSN=&&TMPEXP1,
// UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=8000)
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//*

In Reply to Philip Sevetson:
Michael,

Thanks very much. I appreciate the validation of the basic method.

Commenting out the intermediate SELECTs is fine for me, most of the time. However, since I use DSNTIAUL to write out the final result to a sequential file, I have to remove comments at production execution time. Irritating.

I’m thinking about whether I want to create an artifice on the side to deal with this, or something like that – a final permanent table (four columns; jobname char(8), generated_timestamp timestamp, statement_nbr int, statement_text char 80; unique index on the first three) to which I could write results. Then I’d write them out in a separate DSNTIAUL step, and run them in a third step. It’s a lot more elaborate, but gives full flexibility in the creation of the SQL which actually derives the statements (meaning that I could leave comments in!!).

--Phil

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

International Roaming

Michael Hannan

RE: [DB2 z/OS] SQL writing code / methods
(in response to Patrick Bossman)

Pat,

Thanks for the little tip to use RAND() to force a materialise. That would never have occurred to me.  I probably only ever executed RAND once, just out of curiosity.

Randomised single column Surrogate Ids on every table being a performance nightmare.  Death by Random Getpage. LOL Nice to have a use for RAND.

I do put commas at the start of a line instead of at the end too. Now you have reminded me why I do that. LOL. The reason got lost.
 
In Reply to Patrick Bossman:

Another option is to add RAND() to materialize.


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Dec 12, 2017 - 07:33 AM (Europe/Berlin)

James Campbell

[DB2 z/OS] SQL writing code / methods
(in response to Michael Hannan)
Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your
starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings -
terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text
string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2
allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because
its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.

<rest snipped>

Michael Hannan

RE: [DB2 z/OS] SQL writing code / methods
(in response to James Campbell)

James,

Fair enough, but when we are continually commenting out, and uncommenting back in blocks of the SQL code, it turns out to be easier to leave columns 1 and 2 blank for all normal SQL, and just paste two hyphens over all the lines to be commented out for the moment or paste blanks back over them to uncomment them in. 

Having to terminate comments with a */ is just a little bit aggravating. I am too lazy to do that, yet in REXX or PLI, I am forced to like it or lump it. Many of my text lines currently use all columns up to column 72.

Perhaps a fancy EDIT Macro could be invented to fix up the trailing */ needs.

Michael Hannan
 
In Reply to James Campbell:

Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your
starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings -
terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text
string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2
allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because
its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.




Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

[DB2 z/OS] SQL writing code / methods
(in response to James Campbell)
:: BOGGLE ::

Twenty-six years in this business and I never knew that... D'oh!

Thank you. Commenting will move to the format of '/* comment */', henceforth.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 4:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings - terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2 allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.

<rest snipped>

-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bruce Williamson

RE: [DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)

Me 3, the things I learn on here never cease to amaze me ...

P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and find

Roy Boxwell

[DB2 z/OS] SQL writing code / methods
(in response to Philip Sevetson)
Just watch out for poisening the DSC... yes the comments go in the DSC as well...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 3:19 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

:: BOGGLE ::

Twenty-six years in this business and I never knew that... D'oh!

Thank you. Commenting will move to the format of '/* comment */', henceforth.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 4:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings - terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2 allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.

<rest snipped>

-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----

Marcus Davage

[DB2 z/OS] SQL writing code / methods
(in response to Roy Boxwell)
Classification: Public
Really? *evil grin* Bwah ha haaa...

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
-----Original Message-----
From: Boxwell, Roy [mailto:[login to unmask email]
Sent: 13 December 2017 06:06
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

-- This email has reached the Bank via an external source --


Just watch out for poisoning the DSC... yes the comments go in the DSC as well...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 3:19 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

:: BOGGLE ::

Twenty-six years in this business and I never knew that... D'oh!

Thank you. Commenting will move to the format of '/* comment */', henceforth.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 4:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings - terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2 allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.

<rest snipped>

-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----

-----End Original Message-----




Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

Roy Boxwell

[DB2 z/OS] SQL writing code / methods
(in response to Marcus Davage)
Can also cause several makes of Db2 monitor to kark... cue hideous demonic laughter off stage right...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert


-----Original Message-----
From: Davage, Marcus (ITS Database Services - DB2) [mailto:[login to unmask email]
Sent: Wednesday, December 13, 2017 10:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Classification: Public
Really? *evil grin* Bwah ha haaa...

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator -----Original Message-----
From: Boxwell, Roy [mailto:[login to unmask email]
Sent: 13 December 2017 06:06
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

-- This email has reached the Bank via an external source --


Just watch out for poisoning the DSC... yes the comments go in the DSC as well...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 3:19 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

:: BOGGLE ::

Twenty-six years in this business and I never knew that... D'oh!

Thank you. Commenting will move to the format of '/* comment */', henceforth.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, December 12, 2017 4:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 z/OS] SQL writing code / methods

Why not use SQL comments? And no, double hyphens are DSNTEP2 comments, not SQL
comments.

SQL comments appear between a /* and a */. Of course you need to be careful that your starting /* doesn't start in column 1.

//SYSIN DD *
/* THIS IS A COMMENT
*/
SELECT something
/* choose a table */
FROM somewhere
;
/*

works (or should!) work in all four sample routines.

DSNTEP2/4 and DSNTIAD programatically identify and strips the comment strings - terminating a comment at the first */; DSNTIAUL doesn't care and just passes the entire text string to DB2 during the PREPARE phase and DB2 parses out the comments - and DB2 allows nested SQL comments. Hence

//SYSIN DD *
/* don't do this

/* THIS IS A COMMENT
*/
...
*/
/*

works with DSNTIAUL, you'll get a syntax error on the terminating */ with the others because its 'matching' /* was stripped .

James Campbell

On 11 Dec 2017 at 22:19, Michael Hannan wrote:

>
> Phil,
> Here is my JCL I use to remove double hyphen comment lines, before a DSNTIAUL step.

<rest snipped>

-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----

-----End Original Message-----




Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

-----End Original Message-----