Calling user programs from VISION:Builder to perform DB2 SQL Commands
Article ID: 31051
Updated On:
Products
Issue/Introduction
Introduction:
There are two cases to consider when a subprogram is invoked by VISION:Builder to perform DB2 commands, not VISION:Builder itself.
This discusses how these are handled.
Instructions:
Case 1 - VISION:Builder application IS NOT using DB2 Files (M4OLD, M4CORDn, M4SUBFn).
The user program that is Called from VISION:Builder would need to do the CONNECT, OPEN/CLOSE Plan, and DISCONNECT along with any other SQL Commands. VISION:Builder does not do any SQL Commands when there are no DB2 Files in the application.
Case 2 - VISION:Builder application IS using DB2 Files (M4OLD, M4CORDn, M4SUBFn).
Since VISION:Builder is using DB2 Files and SQL Commands, VISION:Builder will do the CONNECT, OPEN/CLOSE Plan, and DISCONNECT when using CALL Attach. With TSO and IMS Attach, the connection work is done by TSO or IMS control programs.
The user program that is Called from VISION:Builder cannot use any SQL Attach Commands (CONNECT, OPEN, CLOSE, DISCONNECT). The user program can use the other SQL Commands as needed.
You must BIND the VISION:Builder DBRM and the user program DBRM together into one plan that will be used to run the VISION:Builder application.
The user program and VISION:Builder must be using the same Attach Method; TSO, CALL, IMS.
In the test case run here we
- Did a BIND of the VISION:Builder DBRM for TSO Attach and the DBRM for the DB2 Utility DSNTEP2 into a single plan named INJCR001.
- Set up our JCL to run VISION:Builder using TSO Attach.
- Provided JCL for the Utility DSNTEP2, SYSPRINT, SYSIN.
- Added a “CALL MODULE DSNTEP2” to our application. The application is written in ASL (Advanced Syntax Language). The CALL was conditional so it would only execute one-time.
- Executed the VISION:Builder application and got the results we wanted. The standard VISION:Builder reports at M4LIST, and the utility output at SYSPRINT.
Here is our test application and a sample of the output.
//M4RUN EXEC PGM=IKJEFT01,REGION=4M
//STEPLIB DD DSN=ISPJCR1.DEVEL2.LOADLIB,DISP=SHR
// DD DSN=ISPJCR1.COMLIB45.LOADLIB,DISP=SHR
// DD DSN=SASC.C550.LINKLIB,DISP=SHR
// DD DSN=ISPJCR1.RUNLIB.LOAD,DISP=SHR
// DD DSN=DB2TEST.DSNLOAD,DISP=SHR
// DD DSN=DB2TEST.RUNLIB.LOAD,DISP=SHR
// DD DSN=CEE.PROD.V1R5M0.SCEERUN,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(MARKIV) PLAN(INJCR001) LIB('ISPJCR1.DEVEL2.LOADLIB')
END
//SYSTERM DD SYSOUT=*
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//DSNTRACE DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SELECT NAME,CREATOR,BINDDATE,ISOLATION FROM SYSDB2.SYSPLAN
ORDER BY NAME;
SELECT NAME,GRANTEE,GRANTOR,GRANTEETYPE,AUTHHOWGOT,
BINDAUTH,EXECUTEAUTH
FROM SYSDB2.SYSPLANAUTH ORDER BY NAME,GRANTEE;
SELECT NAME,GRANTEE,GRANTOR,GRANTEETYPE,AUTHHOWGOT,
CREATETABAUTH,CREATETSAUTH
FROM SYSDB2.SYSDBAUTH ORDER BY NAME,GRANTEE;
SELECT GRANTEE,GRANTOR,TCREATOR,TTNAME,DBNAME,AUTHHOWGOT
FROM SYSDB2.SYSTABAUTH
ORDER BY GRANTEE,TTNAME;
SELECT * FROM SYSDB2.SYSDATABASE;
SELECT NAME,CREATOR,DBNAME,BPOOL,PARTITIONS,LOCKRULE,STATUS,IMPLICIT,
NTABLES,NACTIVE
FROM SYSDB2.SYSTABLESPACE ORDER BY NAME;
SELECT NAME,CREATOR,TYPE,DBNAME,TSNAME,COLCOUNT,CARD
FROM SYSDB2.SYSTABLES WHERE DBNAME LIKE 'IN%'
ORDER BY NAME,CREATOR;
SELECT NAME,CREATOR,TBNAME,TBCREATOR,UNIQUERULE,COLCOUNT,
CLUSTERING,CLUSTERED,DBNAME,INDEXSPACE
FROM SYSDB2.SYSINDEXES
ORDER BY NAME,CREATOR;
SELECT * FROM SYSDB2.SYSUSERAUTH ORDER BY GRANTEE,TIMESTAMP;
//M4INFO DD DUMMY
//M4LIST DD SYSOUT=*
//M4LIB DD DSN=ISPJCR1.DEVEL2.M4LIB,DISP=SHR
//M4REPO DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//M4SORT DD UNIT=SYSDA,SPACE=(TRK,(1,1))
//M4INPUT DD *
DB2RUN2 RCTEMPL S U S #T R
DB2RUN2 RPLISTCNTL Y
PROC1 ERTODAY
##PROC
; Main Procedure
;
LILDATE: FIELD D 4
FEEDBACK: FIELD C 12
WEEKDAY: FIELD C 80
NAME: FIELD V 22
;
IF F.M4OLD=1
CALL MODULE DSNTEP2
END
;
COMBINE LASTNAME, ',' STORE T.NAME
COMBINE T.NAME, FIRSTNME, MIDINIT BLANKS 1 STORE T.NAME
;
; Convert Birth Date to a Lilian Date using Picture 'YYYY-MM-DD'
;
CALL CEEDAYS USING BRTHDATE, 'YYYY-MM-DD',
T.LILDATE, T.FEEDBACK
;
; Convert Lilian Date to a Format Showing Day of the Week
;
CALL CEEDATE USING T.LILDATE, 'Wwwwwwwwwz, Mmm DD, YYYY',
T.WEEKDAY, T.FEEDBACK
;
CALL REPORT REPORT1 ;Output Report
##PEND
REPORT1 ERTODAY S
REPORT1 E1 F
REPORT1 R1 EMPNO
REPORT1 R1 TNAME
REPORT1 R1 BRTHDATE
REPORT1 R1 TLILDATE
REPORT1 R1 TWEEKDAY 0124
REPORT1 T1 Report Showing Use of LE/370 Routines CEEDAYS and CEEDATE#
REPORT1 T1 #
REPORT1 T1 Field BRTHDATE is Input to CEEDAYS Yielding Field LILDATE#
REPORT1 T1 Field LILDATE is Input to CEEDATE Yielding Field WEEKDAY#
REPORT2 ERTODAYX E
REPORT2 E1 F
REPORT2 R1 FTODAY
REPORT2 R1 FTODAYX
REPORT2 R1 FISDATE
REPORT2 R1 FDATE
REPORT2 R1 FJULIAN
REPORT2 R1 FJULANX
REPORT2 R1 FLILIAN
REPORT2 T1 Report Showing Use of All Date Flags#
*************** BOTTOM OF DATA VISION:Builder Application*********
M4LIST Output
APR 07, 1997 14.45.00
***************************************
* SUMMARY OF GENERATED SQL STATEMENTS
***************************************
--------------------------------------------------------------------------------
FILE>SEGMENT SQL STATEMENT
--------------------------------------------------------------------------------
M4OLD>TEMPL STATEMENT 1
SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,BRTHDATE FROM STA
INF1.INTEMPL X1 ORDER BY EMPNO TAB
APR 07, 1997 14.45.00
** MK4W204 TYPE 0 NUMBER OF MESSAGES PRINTED IS 0.
M4OLD - 32 RECORDS INPUT
M4REPO - 58 RECORDS OUTPUT
M4INPUT - 51 RECORDS INPUT
M4LIST - 225 RECORDS OUTPUT
** MK4ED02 TYPE 0 REQUEST-REPORT1 REPORT-1 REQUESTOR ID-
04/07/97 Report Showing Use of LE/370 Routines CEEDA
Field BRTHDATE is Input to CEEDAYS Yielding
Field LILDATE is Input to CEEDATE Yielding
--------------------------------------------------------------------------------
EMPNO NAME BRTHDATE LILDATE WEEKDAY
--------------------------------------------------------------------------------
000010 HAAS, CHRISTINE I 1933-08-14 08/14/1933 Monday, Aug 14, 1933
000020 THOMPSON, MICHAEL L 1948-02-02 02/02/1948 Monday, Feb 02, 1948
000030 KWAN, SALLY A 1941-05-11 05/11/1941 Sunday, May 11, 1941
000050 GEYER, JOHN B 1925-09-15 09/15/1925 Tuesday, Sep 15, 1925
000060 STERN, IRVING F 1945-07-07 07/07/1945 Saturday, Jul 07, 1945
000070 PULASKI, EVA D 1953-05-26 05/26/1953 Tuesday, May 26, 1953
000090 HENDERSON, EILEEN W 1941-05-15 05/15/1941 Thursday, May 15, 1941
000100 SPENSER, THEODORE Q 1956-12-18 12/18/1956 Tuesday, Dec 18, 1956
000110 LUCCHESI, VINCENZO G 1929-11-05 11/05/1929 Tuesday, Nov 05, 1929
000120 O'CONNELL, SEAN 1942-10-18 10/18/1942 Sunday, Oct 18, 1942
000130 QUINTANA, DOLORES M 1925-09-15 09/15/1925 Tuesday, Sep 15, 1925
000140 NICHOLLS, HEATHER A 1946-01-19 01/19/1946 Saturday, Jan 19, 1946
000150 ADAMSON, BRUCE 1947-05-17 05/17/1947 Saturday, May 17, 1947
000160 PIANKA, ELIZABETH R 1955-04-12 04/12/1955 Tuesday, Apr 12, 1955
000170 YOSHIMURA, MASATOSHI J 1951-01-05 01/05/1951 Friday, Jan 05, 1951
000180 SCOUTTEN, MARILYN S 1949-02-21 02/21/1949 Monday, Feb 21, 1949
000190 WALKER, JAMES H 1952-06-25 06/25/1952 Wednesday, Jun 25, 195
000200 BROWN, DAVID 1941-05-29 05/29/1941 Thursday, May 29, 1941
000210 JONES, WILLIAM T 1953-02-23 02/23/1953 Monday, Feb 23, 1953
000220 LUTZ, JENNIFER K 1948-03-19 03/19/1948 Friday, Mar 19, 1948
000230 JEFFERSON, JAMES J 1935-05-30 05/30/1935 Thursday, May 30, 1935
000240 MARINO, SALVATORE M 1954-03-31 03/31/1954 Wednesday, Mar 31, 195
000250 SMITH, DANIEL S 1939-11-12 11/12/1939 Sunday, Nov 12, 1939
000260 JOHNSON, SYBIL V 1936-10-05 10/05/1936 Monday, Oct 05, 1936
000270 PEREZ, MARIA L 1953-05-26 05/26/1953 Tuesday, May 26, 1953
000280 SCHNEIDER, ETHEL R 1936-03-28 03/28/1936 Saturday, Mar 28, 1936
000290 PARKER, JOHN R 1946-07-09 07/09/1946 Tuesday, Jul 09, 1946
000300 SMITH, PHILIP X 1936-10-27 10/27/1936 Tuesday, Oct 27, 1936
000310 SETRIGHT, MAUDE F 1931-04-21 04/21/1931 Tuesday, Apr 21, 1931
000320 MEHTA, RAMLAL V 1932-08-11 08/11/1932 Thursday, Aug 11, 1932
000330 LEE, WING 1941-07-18 07/18/1941 Friday, Jul 18, 1941
000340 GOUNOT, JASON R 1926-05-17 05/17/1926 Monday, May 17, 1926
** MK4FT03 TYPE 0 END OF REPORT.
** MK4ED02 TYPE 0 REQUEST-REPORT2 REPORT-1 REQUESTOR ID-
04/07/1997 Report Showing Use of All Date F
--------------------------------------------------------------------------------
TODAY TODAYX ISDATE DATE JULIAN JULANX LILIAN
BROWSE -- ISPJCR1S.J08361.M4RUN.M4LIST ------------- LINE 00000275 COL 001 080
COMMAND ===> SCROLL ===> PAGE
--------------------------------------------------------------------------------
040797 04071997 19970407 APR 07, 1997 97097 1997097 04/07/1997
** MK4FT03 TYPE 0 END OF REPORT.
** MK4W204 TYPE 0 NUMBER OF MESSAGES PRINTED IS 4.
M4REPO - 57 RECORDS INPUT
M4LIST - 49 RECORDS OUTPUT
** MK4W209 TYPE 0 1058880 BYTES OF MAIN STORAGE UNUSED DURING REPORTING PHASE
******************BOTTOM OF DATA on M4LIST ********************************
FIRST PART OF OUTPUT AT SYSPRINT FROM UTILITY
PAGE 1
***INPUT STATEMENT: SELECT NAME,CREATOR,BINDDATE,ISOLATION FROM SYSDB2.SYSPLAN
+----------------------------------
| NAME | CREATOR | BINDDATE |
+----------------------------------
1_| INDBEDGE | ISPVNL1 | 910219 |
2_| INIQ57FT | ISPTMR1 | 930712 |
3_| INITPL01 | ISPLAG2 | 921103 |
4_| INITPL03 | ISPLAG1 | 940301 |
5_| INITPL04 | ISPLAG1 | 930811 |
6_| INJCR001 | ISPJCR1 | 970407 |
7_| INJCR002 | ISPJCR1 | 910409 |
8_| INMH01 | ISPOMH1 | 940302 |
9_| INM4CALL | ISPJCR1 | 910104 |
10_| INM4IMS | ISPJCR1 | 900507 |
11_| INM4TSO | ISPJCR1 | 900919 |
12_| INM5TSO | ISPJCR1 | 900814 |
13_| INQASQL1 | ISPTMR1 | 930125 |
. . .
Environment
Component: V:BLDR
Feedback
