Managing Native Stored Procedures and Triggers in Code Pipeline

Perform the tasks in this milestone to enable management of Db2 native stored procedures and triggers in Code Pipeline.

Important

Roles involved:
Code Pipeline Installer
Db2 DBA

This section provides information about the following topics:

Overview
Step 1. Customize CLST WZU2P#PP
Step 2. Use Echo Mode to Verify Db2 Handling
Step 3. Create Objects by Calling DSNTEP2 from the Deploy Procedure
Step 4. Customize WZU2P#PP for Large SQL Statements

Overview

Stored procedures and triggers are Db2 DDL statements. These statements can be managed in Code Pipeline by creating Objects using the Code Pipeline Deploy procedure. The method used depends on the size:

For normal-sized stored procedures, objects are created by calling DSNTEP2 from the Deploy procedure.
For SQL statements that exceed the size limitation of 2,097,152 bytes, a version of CLST WZU2P#PP can be used. It removes the white spaces in the SQL statement to make it smaller. Then it calls DSNREXX to execute the Create of the stored procedure.

This milestone provides sample instructions for both methods.

The process includes the following steps:

Edit the SQL for Environment-specific variables.
Run a drop of the Object. (Should be in the code from developer.)
Create the Object.
Grant the Object.

Define deploy types for stored procedures and triggers with the Deploy Category of DB2. Make sure that Deploy Category is used in CLST WZU@ACI#, the main Deploy exit. Also make sure that WZU@ACI# calls CLST WZU2P#B in the same way as a Db2 bind in the activation of the Deploy. The logic is implemented in CLST WZU2P#PP. In the examples shown, the Deploy Type SPDF is used for stored procedures, and SPTR is used for triggers.

Step 1. Customize CLST WZU2P#PP

In CLST WZU2P#PP, edit the DB2_Bind Processing section adding code similar to that shown as shown in the following figure. The lower part of the figure performs the actual application of the settings defined in the upper part. Edit the sample code’s Environment-specific information as required.

Important

This process can also be performed by using Adaption Cards in the Implementation of the Deploy.

Sample Additional WZU2P#PP Code in DB2_Bind Processing Section

/* DB2 SP Modification, Edit Source to adapt Subsystem specifics */
If membtype = 'SPDF' | membtype = 'SPTR' Then Do
say "Using Inputlib: " dbrmlib
spdflib = strip(dbrmlib,,"'")
Address TSO
"ALLOC F(SPDFIN) DA('"spdflib"("pgmname")') SHR REUS"
"execio * diskr SPDFIN (stem spdfin. finis)"
xssid = strip(ssid,b)
xssid = substr(xssid,4)
toprmin = xssid
select
      when toprmin = 'D' then do
           tssid = 'DBSD'
           tgen = 'DB2DGEN'
           tcos = 'DB2DCOS'
           tcom = 'DB2DCOM'
           tpay = 'DB2DPAY'
           tdba = 'DB2DDBA'
           tsg = 'SGDBX'
           tsyn1 = 'DEV'
           tsyn2 = 'DEV.'
           tfsg = 'SGDBP'
           end
      when toprmin = 'P' then do
           tssid = 'DBSP'
           tgen = 'DB2PGEN'
           tcos = 'DB2PCOS'
           tcom = 'DB2PCOM'
           tpay = 'DB2PPAY'
           tdba = 'DB2PDBA'
           tsg = 'SGDBP'
           tsyn1 = 'PROD'
           tsyn2 = 'PROD.'
           tfsg = 'SGDBP'
           end
      otherwise do
           /* nada */
               end
      end
fssid = 'DBSP'
fgen = 'DB2PGEN'
fcos = 'DB2PCOS'
fcom = 'DB2PCOM'
fpay = 'DB2PPAY'
fdba = 'DB2PDBA'
fsg = 'SGDBP'
fsyn1 = 'PROD'
fsyn2 = 'PROD.'
fsg2 = 'SYSDEFLT'
Do k = 1 TO spdfin.
      If ssid <> 'DBSP' Then Do
           wrkline = spdfin.k
           pos_ssid=INDEX(wrkline,fssid)
           if pos_ssid >0 then wrkline=OVERLAY(tssid,wrkline,pos_ssid)
           pos_gen=INDEX(wrkline,fgen)
           if pos_gen >0 then wrkline=OVERLAY(tgen,wrkline,pos_gen)
           pos_cos=INDEX(wrkline,fcos)
           if pos_cos >0 then wrkline=OVERLAY(tcos,wrkline,pos_cos)
           pos_com=INDEX(wrkline,fcom)
           if pos_com >0 then wrkline=OVERLAY(tcom,wrkline,pos_com)
           pos_pay=INDEX(wrkline,fpay)
           if pos_pay >0 then wrkline=OVERLAY(tpay,wrkline,pos_pay)
           pos_dba=INDEX(wrkline,fdba)
           if pos_dba >0 then wrkline=OVERLAY(tdba,wrkline,pos_dba)
           pos_sg =INDEX(wrkline,fsg)
           if pos_sg >0 then wrkline=OVERLAY(tsg,wrkline,pos_sg)
           pos_sg2=INDEX(wrkline,fsg2)
           if pos_sg2 >0 then wrkline=OVERLAY(tsg2,wrkline,pos_sg2)
           spdfin.k = wrkline
           End
      End

Step 2. Use Echo Mode to Verify Db2 Handling

Db2 handling—such as that for binds and stored procedures—is controlled by a Plan entry on the Modify Application Stream Plan Implementation screen (M.AD/P) for each level in the development lifecycle. To avoid affecting the runtime environment, you can choose to not run the actual bind or create during setup and testing. The M.AD/P screen includes a Do BINDs? field that can be set to Y (Yes), N (No), or E (Echo). When E is specified, Code Pipeline echoes the commands to the log without executing them. The commands are written in the format and order in which they would have been processed. You can then visually verify that the commands are appearing correctly in the log before changing the Do BINDs? field to Y.

Step 3. Create Objects by Calling DSNTEP2 from the Deploy Procedure

For normal-sized stored procedures, objects are created by calling DSNTEP2. Modify DSNTEP2 as shown in the following figure.

Sample DSNTEP2 Code to Create Objects

   Address TSO
/* "ALLOC F(SYSIN) NEW REUSE DELETE ", */
  "ALLOC F(SYSIN) NEW DELETE ",
  " RECFM(F B) LRECL(80) BLKSIZE(6160)",
  " SPACE(1 1) TRACK"

  filerc = Listdsi("SYSPRINT" "file")
  If filerc = 0 Then
    "FREE F(SYSPRINT)"

/* "ALLOC F(SYSPRINT) NEW REUSE DELETE ", */
  "ALLOC F(SYSPRINT) NEW REUSE DELETE ",
  " RECFM(F B) LRECL(133) BLKSIZE(0)",
  " SPACE(1 1) TRACK"

  "execio 0 diskw SYSIN (open"
  "execio * diskw SYSIN (stem spdfin. finis)"

  queue "RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2),
     PARM('SQLTERM(#)') LIB('"$DSNRLIB"')"
  queue "END"
  Invoke_DB2 = "DSN SYSTEM("SSID")"
  "ISPEXEC SELECT CMD("Invoke_DB2") NEST"
  retc=rc
  "FREE F(SYSIN)"
  ecnt = 0
  e204 = 0
  "execio * diskr SYSPRINT (stem syspr. finis)"
  Do k = 1 TO syspr.0
    log = syspr.k
    if retc > 0 then do
      poserr=INDEX(log,'SQLCODE = -')
      if poserr > 0 then do
   /* Since we have DROP in the code, we need to allow one -204 if first time */
         if substr(log,poserr+11,3) = '204' then do
            e204 = e204 + 1
            end
         else do
            ecnt = ecnt + 1
            end
         end
      end
    say log
    If dplogrc = 0 Then
      rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
    End
  if retc > 0 then do
    if ecnt = 0 then retc = 0
    end
  End
End
Else Do /* Bind Process */

Step 4. Customize WZU2P#PP for Large SQL Statements

The CLST WZU2P#PP can be used with SQL statements larger than 2,097,152 bytes to remove white space before calling DSNREXX to Create the stored procedure. Modify WZU2P#PP as shown in the following figure.

Sample WZU2P#PP Code for Large SQL Statements

/* CREATE ONE STATEMENT FOR ALL LINES TO SQLTERM */
/* AND THEN EXECUTE IMMEDIATE ON THAT */
/*----------------------
| enter db2 interfaces
|-----------------------*/
  saverc = 0
  trace o
  'SUBCOM DSNREXX'
  if rc <> 0 then,

  IF RC THEN DO
    RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
    if rc <> 0 then,
      SAY 'RXSUBCOM ADD, RC='RC
  end

  ADDRESS DSNREXX
  if rc <> 0 then,
    SAY 'ADDRESS DSNREXX, RC='RC

  ADDRESS DSNREXX 'CONNECT' SSID
  if rc <> 0 then,
    SAY "ADDRESS DSNREXX CONNECT "SSID", RC="RC
  sqlterm = '#'
  sqlcmnt = '--'
  stmnt = ''
  do x = 1 to spdfin.0
    comnt = pos(sqlcmnt,left(spdfin.x,72))
    if comnt > 0 then do /* If comment on line, pick only before */
      stmnt = stmnt space(left(spdfin.x,comnt-1))
      end
    else do /* else, just take whole line */
      stmnt = stmnt space(left(spdfin.x,72))
      end
    if pos(sqlterm,stmnt) > 0 then do
      stmnt = strip(stmnt,'L') /* If leading spaces, remove */
      stmnt = OVERLAY(' ',stmnt,pos(sqlterm,stmnt)) /* remove END*/
      stmnt = space(stmnt) /* Squeeze a little bit more */
      RUNSTMT = 'EXECUTE IMMEDIATE :stmnt'
      ADDRESS DSNREXX "EXECSQL" RUNSTMT
      trace o
      log = ''
      say log
      If dplogrc = 0 Then ,
        rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
      lstmnt = length(stmnt) /* original length for display */
      stmnt = stmnt||' ' /* add a space for fix process bolow */
      cut = LASTPOS(' ',stmnt,72)
      do while length(stmnt) > 72
        log = left(stmnt,cut)
        say log
        If dplogrc = 0 Then ,
          rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
        stmnt = substr(stmnt,cut+1)
        cut = LASTPOS(' ',stmnt,72)
      end
      log = stmnt
      say log
      If dplogrc = 0 Then ,
        rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
      log = ''
      say log
      If dplogrc = 0 Then ,
        rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
      log='Length of statement:' lstmnt '(Limit 2097152)'
      say log
      If dplogrc = 0 Then ,
        rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
      log = ''
      say log
      If dplogrc = 0 Then ,
        rc=WZZUTI("SETLOG","WZU2P#PP","DD:WZZDPLOG",log)
      call show_sqlca
      If sqlcode > 0 & saverc < 4 then saverc = 4
      If sqlcode < 0 then do
         if pos('DROP ',stmnt) > 0 & sqlcode = -204 then do
    /* Don't Fail on -204 when DROP */
            if saverc < 4 then saverc = 4
            end
         else do
           if saverc < 8 then saverc = 8
           end
         end
      stmnt = ''
      end
    retc = saverc
  end