Mainframe DB2 SQL IN list multiple columns

In DB2 version 7 a feature called row value expression was introduced. A row value expression is a list of columns or constants which you can compare with another list of columns or constants with the same number of elements. You may also compare the list with the result of an IN subselect.

Let me give you an example which hopefully is far more explanatory than a long verbal description:

...
WHERE A = 3
AND B = 'THE SUN'

may be written as:

...
WHERE (A, B) = (3, 'THE SUN')

using a row vaule expression. The above rewrite may save some keyboard work and in other sitiations it may make your SQL more readable. When I explore such new inventions in DB2 I cannot wait to try them out in other contexts. Unfortunately the only other legal context is combined with an IN subselect:

...
WHERE (A, B) IN
( SELECT X, Y
FROM ANOTHER_TABLE
)
When I executed the above SQL statement it took a very, very long time before I got a reply while:...
WHERE EXISTS
( SELECT 0
FROM ANOTHER_TABLE
WHERE (A, B) = (X, Y)
)

gave me a reply right away. Therefore I will recommend you to exercise great care when using a row value expression in combination with an IN subselect. I have not tried it on DB2 version 8 or DB2 9. Maybe these versions have improved. To be honest I cannot see the value of the IN subselect combined with the row value expression because an EXISTS does the job just as good with better performance in most cases. Please note that the IN version allows for non-correlated subselects while the EXISTS version will always be correlated. Please consult the explain output and try the non-correlated version before putting it into production, because it may perform very badly.

Mainframe tools associated with performance

 Below is the list of tools categorized to assist in the management of Mainframe performance

Mainframe Monitoring tools
Omegamon
BMC AMI Ops Monitoring(Formerly Mainview)
Sysview
ASG TMON
 
Code Profiling tools
IBM APA
TRITUNE
Strobe
Macro4 Freezeframe
CA Mainframe Aapplication Tuner(CA MAT)
 
Mainframe Performance Reporting tools
IBM Z IntelliMagic Vision for z/OS
Pivotor from EPS inc
IBM Z Performance and Capacity Analytics (IZPCA)
MXG
CA MICS
IBM Tivoli Decision Support for z/OS(TDSz)
Syncsort™ Capacity Management
Zetaly Service Intelligence​
SAS ITRM
zWorkload Reporter
EasySMF for z/OS
 
DB2 SQL monitoring tools
IBM Query Monitor
BMC Apptune
CA Detector
 
SMF Streaming tools
IBM Common Data Provider for z Systems (CDPz)
Precisely’s Ironstream

Why/When AICA abend happens in CICS region

Following content is copied from https://planetmainframe.com/2023/08/cics-programming-best-practices/

The option ICVR (Interval Control Value Runaway) is the amount of Task time that a task can consume between Calls to CICS. You see CICS is a very friendly system; it just says that you can freely use my services, you just have to talk to me once in a while. However, if you don’t talk to me, then I’m going to assume that you are looping and abend you. How does it do this? When you issue an EXEC CICS command and CICS processes your request, then control returns to your program. CICS then starts a Clock and decrements it while you have the CPU processing your COBOL/Assembler statements. If the Clock falls to zero, CICS, by Interval Control, will abend your task AICA.  Actually, it’s quite clever; it abends the Task with an S0C1 (Operation Exception). ICVR controls this Clock and it’s specified in the Overrides and changeable online by a CEMT SET SYSTEM command. 2000 milliseconds (2 seconds) is the default. This is a system-wide value but can be specified differently for each transaction. 

Dealing with Null Output

The AVG, MIN, MAX, and SUM functions almost always return a null value when there are no matching rows (see No Rows Match for exceptions). One can use the COALESCE function, or a CASE expression, to convert the null value into a suitable substitute. Both methodologies are illustrated below:

Convert null output (from AVG) to zero

SELECT COUNT(*) AS c1
, AVG(salary) AS a1
, COALESCE(AVG(salary),0) AS a2
, CASE
WHEN AVG(salary) IS NULL THEN 0
ELSE AVG(salary)
END AS a3
FROM staff
WHERE id < 10;

ANSWER

C1	A1	A2	A3
0	null	0	0

No Rows Match

How many rows are returned by a query when no rows match the provided predicates? The answer is that sometimes you get none, and sometimes you get one:

 Query with no matching rows (1 of 8)

 SELECT creator

FROM sysibm.systables

WHERE creator = 'ZZZ';

ANSWER: no row

Query with no matching rows (2 of 8)

SELECT MAX(creator)

FROM sysibm.systables

WHERE creator = 'ZZZ';

ANSWER: Null

Query with no matching rows (3 of 8)

SELECT MAX(creator)

FROM sysibm.systables

WHERE creator = 'ZZZ'

HAVING MAX(creator) IS NOT NULL;

ANSWER: no row

Query with no matching rows (4 of 8)

SELECT MAX(creator)

FROM sysibm.systables

WHERE creator = 'ZZZ'

HAVING MAX(creator) = 'ZZZ';

ANSWER: no row

Query with no matching rows (5 of 8)

SELECT MAX(creator)

FROM sysibm.systables

WHERE creator = 'ZZZ'

GROUP BY creator;

ANSWER: no row

Query with no matching rows (6 of 8)

SELECT creator

FROM sysibm.systables

WHERE creator = 'ZZZ'

GROUP BY creator;

ANSWER: no row

Query with no matching rows (7 of 8)

SELECT COUNT(*)

FROM sysibm.systables

WHERE creator = 'ZZZ'

GROUP BY creator;

ANSWER: no row

Query with no matching rows (8 of 8)

SELECT COUNT(*)

FROM sysibm.systables

WHERE creator = 'ZZZ';

ANSWER: 0

There is a pattern to the above, and it goes thus:

• When there is no column function (e.g. MAX, COUNT) in the SELECT then, if there are no matching rows, no row is returned.
• If there is a column function in the SELECT, but nothing else, then the query will always return a row - with zero if the function is a COUNT, and null if it is something else.
• If there is a column function in the SELECT, and also a HAVING phrase in the query, a row will only be returned if the HAVING predicate is true.
• If there is a column function in the SELECT, and also a GROUP BY phrase in the query, a row will only be returned if there was one that matched.

 The above documentation is copied from https://db2-sql-cookbook.org/

Data overlay caused by COBOL reference modification

If the length of the target field in a MOVE statement is greater than or equal to the length of the source field, and the source field has reference modification, and the length of the source field is provided through a variable, and the length of the reference modification is greater than the length of the target field, it will result in data overlay in the subsequent fields of the target field.

By supplying a value of 15 in the SYSIN card for the length field in the program below, we can observe the occurrence of data overlay. 

        IDENTIFICATION DIVISION.               
        PROGRAM-ID. HELLO.                     
        DATA DIVISION.                         
        WORKING-STORAGE SECTION.               
        01 GRP-A.                              
           05 WS-X   PIC X(05) VALUE '12345'.  
           05 FILLER PIC X(05) VALUE 'ABCDE'.  
           05 FILLER PIC X(05) VALUE 'FGHIJ'.  
        01 GRP-B.                              
           05 WS-A    PIC X(05).               
           05 WS-B    PIC X(10).               
        01 WS-LEN     PIC 9(02).               
        PROCEDURE DIVISION.                    
           MOVE SPACES TO WS-B                 
           DISPLAY 'WS-B: ' WS-B.              
           ACCEPT WS-LEN 
           MOVE WS-X (1:WS-LEN) TO WS-A        
           DISPLAY 'WS-B: ' WS-B. 
           STOP RUN.    

Output of the above program

 WS-B:               

 WS-B: ABCDEFGHIJ    

This issue is observed in IBM Enterprise COBOL for z/OS  6.3.0. 

If the length of the target field is less than the length of the source field, then this iissue wont occur

How to transfer a set of text files to a PDS as individual members in the IBM PCOM session

To transfer a set of text files to a PDS as individual members, we need to create a batch file. Let us transfer two text files to PDS USERID.DDF.TOOL. So, put the below two lines in a file and save the batch file with “srl” extension. 

C:\Downloads\ASMEXIT.JCL text~'USERID.DDF.TOOL(ASMEXIT)'
C:\Downloads\BUCKETS.JCL text~'USERID.DDF.TOOL(BUCKETS)'
 
In the Mainframe IBM PCOM session, Select option “6  Command Enter TSO or Workstation commands” form ISPF main menu. From the IBM PCOM Menu, click on “Action” and then click on “Send File to Host”. You will get the below screen. In the below screen, click on “Options” and then you will get “File Transfer Settings” window.
 

 
In the “File Transfer Settings” window, click on “MVS/TSO” tab and select “text” under “Transfer type” drop down box.  Then make sure “ascii” and “crlf” options are ticked in the “File options” and then select “Default” under “Record Format” drop down box.

Now go back to the “Send File to Host” window, click on “Open List” button and select the “srl” batch file that we already created and then click on “Send” button. This will transfer text files one by one to PDS as members.
 
 
 

Generating a report of dynamically called COBOL programs

IBM Enterprise COBOL V6R4 allows you to produce a report of all subroutines called by dynamic call. Use ddname IGZPROUT at the run step of your JCL to generate a report of all dynamically called programs that are compiled with Enterprise COBOL 5 or later.

The report details include the Program Name, Compiler Version, and Time and Date of compilation of the programs. Only programs that are compiled with COBOL 5 or later will be listed. Details of cancelled programs are printed at the end.

Note: If a program is cancelled multiple times, then the report prints it only once.

Recommended attributes for the IGZPROUT ddname are as follows:
DSORG = PS   RECFM = FB   78 = LRECL = 80

Here are the examples of setting the IGZPROUT ddname:
//IGZPROUT DD SYSOUT=*

or
//IGZPROUT DD DSN=dynamic.call.report.dsname,DISP=SHR

or
//IGZPROUT DD DSN=dynamic.call.report.dsname,
// UNIT=SYSDA,SPACE=(TRK,(5,10),RLSE),
// DCB=(DSORG=PS,LRECL=80,RECFM=FB,BLKSIZE=0),
// DISP=(NEW,CATLG,DELETE)

You can get an output report as follows:

*********************** DYNAMICALLY CALLED PROGRAM REPORT ********************
************************* FOR COBOL 5 AND LATER RELEASES *********************

NAME       COMPILER VER.       DATE/TIME COMPILED
-----------------------------------------------------------------------------
PROG1      V06 R02 M00         2021/01/01 15:30:02
PROG2      V06 R02 M00         2021/01/01 15:30:12

The following programs are marked as cancelled at termination:
PROG3      V06 R03 M00         2022/02/13 17:36:29
*************************** END OF PROGRAM REPORT ****************************

Note: For dynamic calls, the program-name in the PROGRAM-ID paragraph or ENTRY statement must be identical to the corresponding program object name or program object alias of the program object that contains the program. If the PROGRAM-ID paragraph or the ENTRY statement is not identical to the corresponding program object (PDSE member) name or program object alias of the program object that contains the program, then the report will list the program-name in the PROGRAM-ID paragraph or the ENTRY statement, as opposed to listing the program object (PDSE member) name or the program object alias name.

A Very good youtube from "Dinosaur Parkour" about this feature can be found in the below link.

https://www.youtube.com/watch?v=OEyTjIR6tW0