Find Sequel Objects Affected by an Expanded Data Field

Occasionally a database field requires a change to its length. This can impact Sequel objects (views, reports, scripts and so on) depending how the object references the field, how the object is used, and the type of output generated by the object.

This document will help you find any of your Sequel objects that use a given field so you can take the necessary steps to modify the objects.

Sequel Host Reports

As Sequel  Host Reports are built over view objects, you need to find the Host Reports that use the modified field.

1. Issue the following command to create a work file containing a list of SEQUEL host report objects:

   DSPRPTD REPORT(LIB/*ALL) TYPE(*SQL) Sequel OUTPUT(*OUTFILE) OUTFILE(MYLIB/REPORTS)
   
   

2. Create a view over this file with the following SQL:

   SELECT DISTINCT rptlib, rptnam 
   FROM mylib/reports(rdsql)
   WHERE UPPER(sql) CONTAINS “FIELD” 
   ORDER BY rptlib, rptnam 
   
   

3. This view produces a list of reports that use the modified field. Each report will have to be opened in Design to ensure there is room to accommodate the new field length.

Sequel Views—Variable on the WHERE Clause

If the modified field is used on the WHERE clause with a variable for record selection, the variable length will have to be increased.

1. Issue the following command to create a work file containing a list of prompted Sequel views:

   DSPVIEWD VIEW(LIBRARY/*ALL) ATTRIB(SQLVIEWP) 
            TYPE(*SQL) OUTPUT(*OUTFILE) OUTFILE(MYLIB/VIEWS) 
   
   

2. Create view over this file with the following SQL:

   SELECT viewlb, viewnm 
   FROM mylib/views(vdsql) 
   WHERE sql CONTAINS “WHERE” AND UPPER(sql) CONTAINS “FIELD” 
   ORDER BY viewlb, viewnm
    

3. This view produces a list of prompted views that use the modified field in the WHERE clause. Each view will have to be opened in Design so the length of the variable can be adjusted to accommodate the new field size.

Sequel Views—Used in Scripts to Replace File Records

For Sequel Scripts, if a modified field is used in a view or SQL statement to replace records in an existing physical file, the file must be deleted to avoid a level check (CPF4131) error. The following process can be used to identify any scripts that use the EXECUTE command and the associated files to delete.

1. Issue the following command to create a work file containing a list of Sequel objects:

   DSPOBJD OBJ(LIB/*ALL) OBJTYPE(*USRSPC) OUTPUT(*OUTFILE) 
           OUTFILE(MYLIB/OBJECTS)
    

2. Create ScriptB (this script is used by ScriptA below and must be created first) with the following commands:

   10 MONMSG 
   20 DSPSCRIPTD SCRIPT(yourlib/&SCPTNM) TYPE(*SRC) OUTPUT(*OUTFILE) 
                 OUTFILE(QTEMP/SCPTNM) 
   30 EXECUTE SQL(‘select “&&scptnm” len(10) name(scptnm), *.1 
                   from qtemp/scptnm’) 
              OUTFILE(mylib/Scripts2) MBROPT(*ADD)
   40 DLTF FILE(QTEMP/SCPTNM)
    

3. Create ScriptA with the following commands:

   10 MONMSG 
   20 RUNCMD CMD(‘runscript script(yourlib/ScriptB) 
             SETVAR((_&&scptnm ‘‘_&&odobnm’’))’) 
             SQL(‘select odobnm from mylib/objects 
                  where odobat contains “SQLSCRIPT”’)
   
   

4. Run ScriptA. The following process will occur:
   • ScriptA, using the RUNCMD, will repetitively run ScriptB as many times as there are records in the OBJECTS file (created in Step 1) with an attribute of SQLSCRIPT.
      
   • Every time ScriptB runs, it places the command list and the corresponding name for each script in a work file—SCRIPTS2.
      
5. When ScriptA completes, query the SCRIPTS2 file with the following command:

   DISPLAY (‘SELECT DISTINCT SCPTNM, SRCDTA FROM MYLIB/SCRIPTS2 
             WHERE UPPER(SRCDTA) CONTAINS “EXECUTE”’)

   This listing displays each script name that contains an EXECUTE command along with the view name or SQL statement it uses.
    
6. The View names from the script listing in Step 5 can be cross-referenced against a list of views that use the modified field. First, issue the following command to create a work file containing a list of Sequel views:

   DSPVIEWD VIEW(LIBRARY/*ALL) ATTRIB(*ALL) TYPE(*SQL) OUTPUT(*OUTFILE) 
            OUTFILE (MYLIB/ALLVIEWS)
    

7. Create a view over this file with the following SQL:

   SELECT DISTINCT viewlb, viewnm 
   FROM mylib/views(vdsql) 
   WHERE UPPER(sql) CONTAINS “FIELD” 
   ORDER BY viewlb, viewnm 
   
   

8. For every view name in this view list that matches a VIEW parameter from an EXECUTE command in the script list (step 5), you will have to delete the file referenced in the OUTFILE parameter to avoid a level check error (you only have to delete the file once).

Client Reports (CRO) and Client Tables

CRO reports and client tables that use a modified field must be manually identified and changed.

Sequel Host Tables

As Sequel Host Tables are built over view objects, you need to find the Host Tables that use the modified field.

1. Issue the following command to create a work file containing a list of SEQUEL host report objects:

   DSPTBLD TABLE(LIB/*ALL) TYPE(*SQL) OUTPUT(*OUTFILE) 
           OUTFiLE(MYLIB/TABLES) 
   
   

2. Create a view over this file. The SQL is as follows:

   SELECT DISTINCT tbllib, tblnam 
   FROM qtemp/tables(tdsql) 
   WHERE sql CONTAINS “FIELD” 
   
   

3. This view produces a list of tables that use the modified field. Each table will have to be opened in Design to ensure there is room to accommodate the new field length.