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.
As Sequel Host Reports are built over view objects, you need to find the Host Reports that use the modified field.
DSPRPTD REPORT(LIB/*ALL) TYPE(*SQL) Sequel OUTPUT(*OUTFILE) OUTFILE(MYLIB/REPORTS)
SELECT DISTINCT rptlib, rptnam FROM mylib/reports(rdsql) WHERE UPPER(sql) CONTAINS “FIELD” ORDER BY rptlib, rptnam
If the modified field is used on the WHERE clause with a variable for record selection, the variable length will have to be increased.
DSPVIEWD VIEW(LIBRARY/*ALL) ATTRIB(SQLVIEWP)
TYPE(*SQL) OUTPUT(*OUTFILE) OUTFILE(MYLIB/VIEWS)
SELECT viewlb, viewnm FROM mylib/views(vdsql) WHERE sql CONTAINS “WHERE” AND UPPER(sql) CONTAINS “FIELD” ORDER BY viewlb, viewnm
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.
DSPOBJD OBJ(LIB/*ALL) OBJTYPE(*USRSPC) OUTPUT(*OUTFILE)
OUTFILE(MYLIB/OBJECTS)
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)
10 MONMSG 20 RUNCMD CMD(‘runscript script(yourlib/ScriptB) SETVAR((_&&scptnm ‘‘_&&odobnm’’))’) SQL(‘select odobnm from mylib/objects where odobat contains “SQLSCRIPT”’)
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.
DSPVIEWD VIEW(LIBRARY/*ALL) ATTRIB(*ALL) TYPE(*SQL) OUTPUT(*OUTFILE)
OUTFILE (MYLIB/ALLVIEWS)
SELECT DISTINCT viewlb, viewnm FROM mylib/views(vdsql) WHERE UPPER(sql) CONTAINS “FIELD” ORDER BY viewlb, viewnm
CRO reports and client tables that use a modified field must be manually identified and changed.
As Sequel Host Tables are built over view objects, you need to find the Host Tables that use the modified field.
DSPTBLD TABLE(LIB/*ALL) TYPE(*SQL) OUTPUT(*OUTFILE)
OUTFiLE(MYLIB/TABLES)
SELECT DISTINCT tbllib, tblnam FROM qtemp/tables(tdsql) WHERE sql CONTAINS “FIELD”
Still have questions? We can help. Submit a case to Technical Support.