Database Utilities

This chapter describes database utilities.

1. Overview

The following is a list of database utilities. Each utility is described in detail below.

Program Name Description

DSNMTV01

Connects to a database when an application uses access to the database such as ESQL or ODBC.

DSNTEP2

Executes database SQL queries.

DSNTIAD

Executes Data Manipulation Language (DML) that inquires and updates database information.

DSNTIAUL

Unloads table data from DB2 database to OpenFrame data set through IKJEFT01 utility.

DSNUTILB

Generates commands to the database.

INZUTILB

Unloads DB2 table data using SQL specified in SYSIN.

2. DSNMTV01

The utility processes database connection for OpenFrame HiDB when DLI/I call is made while an application is accessing the database via ESQL or ODBC.

2.1. DD Statements

The following describes each DD statement.

Statement Description

DDITV02 DD

Specifies the connection information of the database and the application to execute.

The following are the dataset parameters.

SSN,LIT,ESMT,RTT,REO,CRC,CONN,PLAN,PROG

The following describes each parameter and whether or not it is supported by DSNMTV01.

  • SSN: Database system name.

  • LIT: Language Interface Token. This is ignored in OpenFrame.

  • ESMT: Initialization module name when using DB2. This is ignored in OpenFrame.

  • RTT: RTT (Resource Translation Table). This is ignored in OpenFrame.

  • REO: REO (Region Error Option). This is ignored in OpenFrame.

  • CRC: CRC (Command Recognition Character). This is ignored in OpenFrame.

  • CONN: Connection name. This is ignored in OpenFrame.

  • PLAN: DB2 plan name. This is ignored in OpenFrame.

  • PROG: Application program name.

2.2. Command Usage

You cannot use EXEC PGM to directly call DSNMTV01 in JCL. To make a DL/I call in JCL through DFSRRC00, DSNMTV01 must be set to MBR.

//JOB01   JOB  CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//JOBLIB  DD   DSN=SYS1.COBLIB,DISP=SHR
//STEP    EXEC PGM=DFSRRC00,
//             PARM='DLI,DSNMTV01,HOSPPSB,,0000,,0,,N,O,T'
//SYSOUT  DD   SYSOUT=*
//DDITV02 DD   DSN=DSNMTV01.PARMLIB(SAMPLE),DISP=SHR
//*

2.3. Examples

The following example executes DFSRRC00 and sets PSB to HOSPPSB and MBR to DSNMTV01. DFSRRC00 loads PSB information and calls DSNMTV01 to read the database connection information in DB2Q from dsnmtv01. It connects to the database and then executes the TESTPGM program.

<JCL>

//JOB01   JOB  CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//JOBLIB  DD   DSN=SYS1.COBLIB,DISP=SHR
//STEP    EXEC PGM=DFSRRC00,
//             PARM='DLI,DSNMTV01,HOSPPSB,,0000,,0,,N,O,T'
//SYSOUT  DD   SYSOUT=*
//DDITV02 DD   DSN=DSNMTV01.PARMLIB(SAMPLE),DISP=SHR
//*

<DDITV02 DD>

DB2Q,SYS1,DSNMIN10,,A,-,MP13J120,TESTPGM,TESTPGM

2.4. Environment Configuration

You can configure the sections of the dsnmtv01 subject in the OpenFrame configuration to specify database name and connection information.

  1. For more about the configuration, refer to OpenFrame Configuration Guide.

  2. In DSNMTV01, the libtdbconnsw module is used to connect to the database. The libtdbconnsw module must link the library corresponding to the target database.

2.5. Return Codes

The following are return codes returned by DSNMTV01 from executing user applications.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The following errors may occur in DSNMTV01.

    Code Description

    12

    Occurs when:

    • DDITV02 DD is missing.

    • Dataset specified in DDITV02 DD is in an invalid format.

    • Database connection fails.

    • Application is missing or could not be executed.

3. DSNTEP2

The utility executes database SQL queries.

It supports SELECT, DELETE, UPDATE, and INSERT queries as well as COMMIT and ROLLBACK statements. After an SQL execution, the COMMIT command is internally executed and then the utility is terminated.

3.1. DD Statements

The following describes each DD statement.

Statement Description

SYSTSPRT DD

Specifies a data set to print the processing result or error message in the process of executing DSNTEP2 in IKJEFT01.

SYSTSIN DD

Specifies the DSN command and RUN command to execute DSNTEP2 in IKJEFT01.

SYSPRINT DD

SYSOUT DD

Specifies the output data set to print a message.

Both for SYSOUT DD and SYSPRINT DD, all outputs from DSNTEP2 are used. If both SYSOUT DD and SYSPRINT DD are set, SYSOUT DD is used.

SYSIN DD

Specifies a data set that contains a database SQL query or defines an SQL query directly in this statement.

If the LRECL value of the data set used as SYSIN is not equal to 80, or the input record contains a character after the 72th line, '12' is returned as the utility error code.

3.2. Command Usage

To run the DSNTEP2 command, the JCL EXEC statement must include IKJEFT01 and the DSN and RUN commands must be specified in SYSTSIN DD.

For information about SYSTSIN, refer to the command described in IKJEFT01.

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

3.3. Examples

In the following example, IKJEFT01 connects to the database system and runs DSNTEP2 to execute the database SQL query specified in SYSIN DD. The execution result is output to SYSPRINT.

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
  SELECT * FROM DSN8810.PROJ;
/*

The previous command produces the following result.

###### DSNTEP2 DB INFO : USERNAME=tibero,PASSWORD=****,DATABASE=tb_rbtest
##### time check - begin #####
PAGE    1
***INPUT STATEMENT:
    SELECT *
    FROM DSN8810.PROJ;
       +-----------------------------------+
       ! COLONE               ! COLTWO     !
       +-----------------------------------+
     1_! AAA                  !        392 !
     2_! AAA                  !        105 !
       +-----------------------------------+
SUCCESSFUL RETRIEVAL OF          2 ROW(S)
****************************************** Bottom of Data ******************************************
##### time check - end : elapsed time(sec) = 0.000983 #####

The following example enters the UPDATE query to SYSIN DD to print the execution result to SYSPRINT.

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
  UPDATE TEST_TABLE SET COLONE = 'TESTVALUE'
/*

The previous command produces the following result.

###### DSNTEP2 DB INFO : USERNAME=tibero,PASSWORD=****,DATABASE=tb_rbtest
##### time check - begin #####
PAGE    1
***INPUT STATEMENT:
    UPDATE TEST_TABLE
    SET COLONE = 'TESTVALUE' ;
NUMBER OF ROWS AFFECTED 2
****************************************** Bottom of Data ******************************************
##### time check - end : elapsed time(sec) = 0.000350 #####

3.4. Environment Configuration

  • Database Connection Information

    The database name and connection information are set in the ikjeft01 subject, SYSTEM:{system} section of the OpenFrame environment setting. The connection information corresponding to the system name specified by the DSN command in IKJEFT01 is used.

    For more information about the configuration of the ikjeft01 subject, refer to OpenFrame Configuration Guide.

3.5. Return Codes

The DSNTEP2 utility program returns the following results after executing a Batch application.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The DSNTEP2 utility program returns the following codes.

    Code Description

    0

    The command is successfully executed. Some informative messages can be printed along with the code.

    4

    The following issues exist.

    • A warning occurred while the database is running.

    • No record is affected by UPDATE/DELETE queries.

    8

    The following issues exist.

    • The database is not specified.

    • SYSREC00 DD is not specified.

    • SYSIN DD is not specified.

    • An error occurred during the SQL execution.

    12

    The following issues exist.

    • An error occurred while using an internal library.

    • The database connection failed.

    • The LRECL value specified for SYSIN does not match, or a character exists beyond the 72th line.

4. DSNTIAD

The utility executes Data Manipulation Language (DML) that inquires and updates database information. It is used to execute DML not only in DB2 but also in Oracle and Tibero.

4.1. DD Statements

The following describes each DD statement.

Statement Description

SYSTSPRT DD

Specifies the data set to print the processing result or error message in executing DSNTIAD in IKJEFT01.

SYSTSIN DD

Specifies DSN command and RUN command to execute DSNTIAD in IKJEFT01.

SYSPRINT DD

SYSOUT DD

Specifies the output data set to print message.

Both for SYSOUT DD and SYSPRINT DD, all outputs from DSNTIAD are used. If both SYSOUT DD and SYSPRINT DD are set, SYSOUT DD is used.

SYSIN DD

Specifies a data set that contains a database SQL query or defines an SQL query directly in this statement.

A line can have up to 72 characters. If a statement is longer than 72 characters, use the caret (^) as the line continuation character.

4.2. Command Usage

To run the DSNTIAD command, the JCL EXEC statement must include IKJEFT01 and the DSN and RUN commands must be specified in SYSTSIN DD. For the usage of SYSTSIN, refer to the command usage described in section IKJEFT01.

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 RUN PROGRAM(DSNTIAD) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

4.3. Examples

In the following example, IKJEFT01 connects to the database system and runs DSNTIAD to execute the database SQL query specified in SYSIN DD. The execution result is output to SYSPRINT.

DSNTIAD supports INSERT, UPDATE, and DELETE commands. Multiple SQL statements can be included in a single control statement. If an error occurs in ten SQL statements from among a large number of SQL statements, an error is returned.

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAD) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
INSERT INTO DSNTAID_GROUP(GROUPNAME, OWNER, SUPGROUP, SUBGRPS, MODEL,
        DATA, CREATION, FLAGS)
VALUES('TESTGRP3', 'JUNG', NULL, NULL, NULL, NULL, '20101005', 0);
DELETE FROM DSNTAID_GROUP WHERE GROUPNAME = 'TESTGRP2';
UPDATE DSNTAID_GROUP SET OWNER = 'TEST01' WHERE GROUPNAME = 'TESTGRP1';
/*

4.4. Environment Configuration

You can configure the database name and connection information in the ikjeft01 subject and SYSTEM:{system} section keys in OpenFrame configuration.

For more about the configuration of ikjeft01 subject, refer to OpenFrame Configuration Guide.

4.5. Return Codes

The following are return codes returned by DSNTIAD from executing batch applications.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The following errors may occur in DSNTIAD.

    Code Description

    0

    Successful. Returned with an informative message.

    4

    Warning occurred while executing a SQL in the database or result set is missing.

    8

    Error occurred while executing a SQL on the database.

    12

    Invalid SQL is specified in SYSIN.

4.6. Other Considerations

DSNTIAD has a version using Open DataBase Connectivity (ODBC) and Oracle Call Interface (OCI). The following describes how to support DSNTIAD in OpenFrame.

  • ODBC is a standard interface that eliminates dependencies between DBMS and applications to enable applications to transparently perform database operations regardless of the DBMS type. It requires the ODBC manager and the ODBC driver.

  • OCI is an API provided by Oracle DBMS. It requires OCI library included in Oracle client.

    The ODBC version supports DBMSs, such as Tibero, DB2 and Oracle. The OCI version only supports Oracle.

5. DSNTIAUL

Unloads DB2 table data to OpenFrame dataset using SQL specified in SYSIN with IKJEFT01 utility.

Basically, it executes the same operation as INZUTILB. INZUTILB can be directly executed in the EXEC statement, but DSNTIAUL is executed in IKJEFT01.

5.1. DD Statements

The following describes each DD statement.

Statement Description

SYSIN DD

Specifies the input data set to convert database tables to a UNIX file.

SYSPRINT DD

Specifies the output data set for message output. If both SYSOUT and SYSPRINT DD exist, the message output by IKJEFT01 is printed to SYSOUT, and the message output by DSNTIAUL is printed to SYSPRINT. In other cases, both IKJEFT01 and DSNTIAUL output a message to SYSPRINT or SYSOUT DD.

SYSTSIN DD

Specifies the DSN and RUN commands for running DSNTIAUL.

SYSPUNCH DD

Sets up the data set that stores information that is used when restoring from a table structure backup file.

If this is not set, DUMMY is used.

SYSRECnn DD

Specifies the output data set.

A single run of DSNTIAUL can create a maximum of 100 backup files in the output data set. Following the input data set’s order, each SELECT statement in SYSIN DD and database table is exported to a new data set. Therefore, the number of output data sets must match the number of SELECT statements and database tables in the input data set.

The number of output data set is specified using nn, which must be between 00 and 99.

If the data set specified in this DD is a newly created data set and a separate LRECL is not specified through JCL, the LRECL of the downloaded data set is determined according to the number and length of columns in the table.

5.2. Command Usage

To execute DSNTIAUL, specify IKJEFT01 as follows in the EXEC statement of JCL and the DSN and RUN commands in SYSTSIN DD as follows, and specify the 'SQL' parameter with the PARM command. For the usage of SYSTSIN, refer to the command usage described in section IKJEFT01.

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD') PARM('SQL')
//*

If you do not use an SQL query and want to perform UNLOAD using only the table name, do not use a separate PARM command.

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

5.3. Examples

In the following example, IKJEFT01 connects to the database system first and then executes DSNTIAUL. DSNTIAUL backs up table data and table structure to DSN8UNLD.SYSREC00 and DSN8UNLD.SYSPUNCH, respectively, from the DSN8810.PROJ database table.

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD') PARM('SQL')
//*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//         UNIT=SYSDA,SPACE=(32760,(1000,500)),
//         DISP=(NEW,CATLG),VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//         UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(NEW,CATLG),
//         VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
 SELECT * FROM DSN8810.PROJ
/*

The following is the modification of SYSTSIN and SYSIN to download using the table name without using SQL in the above example.

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//         UNIT=SYSDA,SPACE=(32760,(1000,500)),
//         DISP=(NEW,CATLG),VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//         UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(NEW,CATLG),
//         VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
 DSN8810.PROJ
/*

5.4. Environment Configuration

You can configure the database name and connection information to the SYSTEM:{system} section keys of ikjeft01 subject in the OpenFrame configuration.

For more information about ikjeft01 subject configuration, refer to OpenFrame Configuration Guide.

5.5. Return Codes

The following are return codes returned by DSNTIAUL from executing batch applications.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The following errors may occur in DSNTIAUL.

    Code Description

    0

    Successful. Returned with an informative message.

    4

    Warning occurred while executing a SQL in the database or result set is missing.

    8

    Occurs when:

    • Database information is missing.

    • SYSREC00 DD is missing.

    • SYSIN DD is missing.

    • Error occurred while executing a SQL.

6. DSNUTILB

The utility issues commands on the database.

OpenFrame supports the LOAD command, UNLOAD command, QUIESCE command, and TEMPLATE command. The LOAD command is used to load data stored in a data set into a table in the database. The UNLOAD command is used to save records existing in a database table to a data set. The QUIESCE command is used to specify a checkpoint in the target database.And the TEMPLATE command is used to allocate the PDS data set to be used for UNLOAD.

The DSNUTILB utility for Tibero requires a copybook file corresponding to an input or output dataset name or table name when executing the LOAD or UNLOAD command. This copybook allows for the processing of packed and zoned decimal values of a 'DECIMAL' column in DB2 for Tibero.

  1. The QUIESCE command has restrictions on DSNUTILB support due to the system structure, so it is provided in the form of calling a user-defined module.

  2. The copybook file name required by DSNUTILB for Tibero when executing the LOAD or UNLOAD command corresponds to the dataset name specified in INDDN or UNLDDN option. If the corresponding copybook does not exist, the utility will use the copybook associated with the table name. If neither copybook is found, an error will occur.

6.1. DD Statements

The following describes each DD statement.

Statement Description

SYSIN DD

Specifies command to execute on the database.

SYSPRINT DD

Output data set where output messages are stored.

Since the LOAD and UNLOAD commands require you to specify the target DD in SYSIN, you must specify the DD name that is set in the INDDN clause of SYSIN.

6.2. Command Usage

This section describes the DSNUTILB commands.

EXEC

Use the following line in the JCL EXEC statement to execute DSNUTILB.

//STEP EXEC PGM=DSNUTILB[,PARM='system,[uid],[utproc]']
Parameter Description

system

Specifies a system name, and retrieves user information of the system from SYSTEM:{system} section of ikjeft01 subject in OpenFrame configuration.

uid

UID used to call a stored procedure when DB2 is installed on mainframe. Otherwise, this parameter is ignored.

utproc

Ignored.
LOAD

The syntax of the LOAD command is as follows:

LOAD DATA
    INDDN ddname1
    [REPLACE]
    INTO TABLE table_name [field_name [POSITION ( field_position : field_length )]]
    [DELIMITED [COLDEL del_char1][CHARDEL del_char2][DECPT del_char3]]
    [WORKDDN (ddname2, ddname3)]
    [ENFORCE NO]
    [DISCARDS discard_number]
    [DISCARDDDN discard_ddname]
    [ERRDDN SYSERR]
    [MAPDDN SNAP]
Parameter Description

INDDN

Specifies the DD name that designates the data set of the LOAD target.

DELIMITED

This is used when the record format of the data set to be LOAD is in the form of a character string based on the delimiter. If the data set created using the UNLOAD command or the DSNTIAUL utility is created through the 'DELIMITED' option, it can be used. When this option is used, the length and position information specified in the field information are ignored.

  • COLDEL: Specifies the column data separator. Unless otherwise specified, use comma (,).

  • CHARDEL: Specifies the delimiter to be used for data representing a string. Unless otherwise specified, use double quotation (" ").

  • DECPT: Specifies the decimal separator for numeric data. Unless otherwise specified, use period (.).

DISCARDS

Specifies the available number of data set records that have failed LOAD. If the number of records that failed to LOAD exceeds this number, the utility returns an error and exits. If not specified, 0 is used. If it is 0, LOAD is continued regardless of the number of records that LOAD failed.

DISCARDDN

Specifies the DD name that records the data set record that failed LOAD. If not specified, 'SYSDISC' is used. Failure records are not recorded unless a separate data set for DISCARDDN is specified in JCL.

REPLACE

Specifies whether to delete the table of the LOAD target.

If REPLACE is specified, delete the existing table data, and if not specified, do not delete the data.

INTO TABLE

Specifies the LOAD target table name and column information. In the case of fields, location information and length information in the data set are specified. If field information is not specified, the position information of each column to be read from the data set is determined according to the order and length of the columns in the table.

WORKDDN, ENFORCE, ERRDDN, and MAPDDN items are not supported in OpenFrame.
UNLOAD

The syntax of the UNLOAD command is as follows:

UNLOAD DATA
    UNLDDN ddname1
    PUNCHDDN  ddname2
    FROM TABLE (table_name [field_name field_type(field_length) [CLOBF template_ddname] [EXTERNAL] ])
    [WHEN (where_condition)]
    [DELIMITED [COLDEL del_char1][CHARDEL del_char2][DECPT del_char3]]
Parameter Description

UNLDDN

Specifies the DD name that designates the data set of the UNLOAD target.

PUNCHDDN

When UNLOAD is executed, the DD name of the data set to record the column name, length, and position belonging to the TABLE is specified. This data set statements can be used for the LOAD command.

DELIMITED

Specifies the record format as a string format based on the delimiter when performing UNLOAD. For data sets that have been UNLOADed using this option, the DELIMITED option must be used when the LOAD command is executed. When this option is used, length and location information is not written to PUNCHDDN DD.

  • COLDEL: Specifies the column data separator. Unless otherwise specified, use comma (,).

  • CHARDEL: Specifies the delimiter to be used for data representing a string. Unless otherwise specified, use double quotation (" ").

  • DECPT: Specifies the decimal separator for numeric data. Unless otherwise specified, use period (.).

FROM TABLE

Specifies the UNLOAD target table name. If you want to select the field for download, specify the field name, type, and length information.

  • CLOBF template_ddname: When the field type to be downloaded is LOB, creates a separate PDS through the TEMPLATE statement and downloads it by designating this keyword and template_ddname.

  • EXTERNAL: Downloads the data in a string format. For example, if EXTERNAL is used for a DECIMAL type column, it is downloaded by expressing a number as a string, not packed decimal data.
QUIESCE

The syntax of the QUIESCE command is as follows:

QUIESCE TABLESPACE tablespace_name
        ...
Parameter Description

TABLESPACE

Specifies the target tablespace name to process QUIESCE.
TEMPLATE

The syntax of the TEMPLATE command is as follows:

TEMPLATE ddname DSN 'dsname' DSNTYPE(dsntype) UNIT(unit) SPACE(primary,secondary) [CYL]
Parameter Description

DSN

Specifies the data set name to be used during the UNLOAD command, and allocates it with ddname. This data set is used when downloading a LOB type field in the FROM TABLE statement of the UNLOAD command.

DSNTYPE

Specifies the type of data set to be allocated. Currently, only PDS is supported.

UNIT, SPACE, and CYL are not supported in OpenFrame.

6.3. Examples

This section describes examples of each command.

LOAD

The following is an example of loading the DSN8UNLD.SYSREC00 file backed up as a UNIX file with the information described in SYSIN DD by executing the DSNUTILB utility program.

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNLOAD  EXEC PGM=DSNUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DSN=DSN8UNLD.SYSREC00,DISP=(SHR,KEEP)
//SYSIN    DD *
LOAD DATA
 INDDN SYSREC
 REPLACE
 LOG NO
 SORTDEVT DISK
 INTO TABLE
 TEST_TABLE
 (
 PO_NBR POSITION ( 1: 11 ) CHAR
 )
 WORKDDN (SYSUT101,SORTOUT)
 ENFORCE NO
 DISCARDDN SYSDISC
 ERRDDN SYSERR
 MAPDDN SMAP
/*
//
UNLOAD

The following is an example of executing the DSNUTILB utility program, allocating SYS1.TSTLIB PDS using the TEMPLATE statement, and then unloading to SYSREC00 DD. At this time, DATA_XML Column downloads LOB type column data as a member called 'DATA_XML' in SYS1.TSTLIB PDS.

//ULDT1    JOB
//STP1     EXEC PGM=DSNUTILB,PARM='DBTW,A,B'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD DSN=ULDTEST.ULD,DISP=(NEW,CATLG,DELETE)
//SYSIN    DD *
TEMPLATE LOBFRW DSN 'SYS1.TSTLIB'
                DSNTYPE(PDS) UNIT(3390)
                SPACE(50,250) CYL
UNLOAD DATA FROM TABLE DATA_UNLOAD HEADER NONE
  (
   NUMBER     CHAR(10)
  ,QUOTE_ID       DECIMAL
  ,CLASS_ID       CHAR(08)
  ,DATA_XML     VARCHAR CLOBF LOBFRW
  ,ADDED_DATE     TIMESTAMP EXTERNAL(26)
  ,ADDED_LOGON    CHAR(15)
  ,REVISED_DATE   TIMESTAMP EXTERNAL(26)
  ,REVISED_LOGON  CHAR(15)
  )
  WHEN (ADDED_DATE >= (CURRENT_TIMESTAMP - 1 YEAR))
       UNLDDN     SYSREC00
       PUNCHDDN   SYSPUNCH
       DELIMITED  COLDEL X'09'
       SHRLEVEL CHANGE
/*
QUIESCE

The following is an example of executing the DSNUTILB utility program to execute QUIESCE in the tablespace named DEFSPACE.

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNLOAD  EXEC PGM=DSNUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
 QUIESCE TABLESPACE DEFSPACE
/*
//

6.4. Environment Configuration

  • Database Connection Information

    The name and access information of database is specified in SYSTEM:{system} section keys of ikjeft01 subject in OpenFrame configuration.

    $ ofconfig list -n NODE1 -s ikjeft01 -sec "SYSTEM:DBPA"

===================================================================================
    SUBJECT     |    SECTION    |         KEY          |            VALUE
===================================================================================
    ikjeft01    |  SYSTEM:DBPA  |       DATABASE       |           oframe3
                |               |        DBAUTH        |            PUBLIC
                |               |        DBTYPE        |            TIBERO
                |               |       INSTANCE       |           oframe3
                |               |       PASSWORD       |             tmax
                |               |       USERNAME       |            tibero
===================================================================================

  • Copybook Path required by DSNUTILB for Tibero

    The DSNUTILB utility for Tibero requires a copybook file for each table to process a 'DECIMAL' column in DB2 when executing the UNLOAD or LOAD command. The copybook file path is specified in DIRECTORY section keys of dbutil subject in OpenFrame configuration.

    $  ofconfig list -n NODE1 -s dbutil -sec DIRECTORY -k DBUTIL_COPYBOOK_DIR

===================================================================================
    SUBJECT     |    SECTION    |         KEY          |           VALUE
===================================================================================
     dbutil     |   DIRECTORY   | DBUTIL_COPYBOOK_DIR  |   ${OPENFRAME_HOME}/data
===================================================================================

  • Module for QUIESCE Command

    The command operates by calling a user-defined module. When QUIESCE is processed, the PATH of the module that performed QUIESCE processing is described in the dbutil subject of OpenFrame environment setting.

    $ ofconfig list -n NODE1 -s dbutil -sec DSNUTILU

===================================================================================
    SUBJECT     |    SECTION    |         KEY        |           VALUE
===================================================================================
    dbutil      |   DSNUTILU    |        PATH        |   ${OPENFRAME_HOME}/temp
===================================================================================

  • User-defined Procedure Prototype for QUIESCE

    The QUIESCE user-defined function should be defined as follows. In DSNUTILB, the uid from the PARM specified in the EXEC statement is delivered to the uid_name, and the statement specified in the SYSIN is delivered to the statement.

    int DSNUTILU(char *uid_name, char *statement)
{
    ...
}

  1. For more about OpenFrame configuration, refer to OpenFrame Configuration Guide.

  2. In DNSUTILB, if it is not for Tibero, the libtdbconnsw module is used to connect to the database. Therefore, in the case of the LOAD command, the libtdbconnsw module must link the library corresponding to the target database. When QUIESCE is used, DSNUTILB does not separately connect to the database.

6.5. Return Codes

The following are return codes returned by DNSUTILB from executing batch applications.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The following errors may occur in DSNUTILB.

    Code Description

    0

    Successful. Returned with an informative message.

    4

    Warning occurred while executing database.

    8

    Occurs when:

    • Invalid SYSIN.

    • Database information is missing.

    • Copybook corresponding to the table is missing in DSNUTILB for Tibero.

    • External module for executing QUIESCE is missing.

7. INZUTILB

The utility unloads table data from DB2 database into OpenFrame data set and specifies the SQL to be unloaded in the SYSIN data set.

7.1. DD Statements

The following describes each DD statement.

Statement Description

SYSIN DD

SELECT SQL to execute on DB2.

SYSREC DD

Output dataset to store the SQL results.

7.2. Command Usage

This section describes the commands of INZUTILB.

EXEC

To execute INZUTILB, specify the following in the EXEC statement of JCL.

//STEP EXEC PGM=INZUTILB
SYSIN DD

Specify the SQL to execute on DB2 as follows:

//SYSIN DD *
 SELECDT * FROM EMPLOYEE
/*

7.3. Examples

The following example executes INZUTILB to unload data from the DB2 EMPLOYEE table to the TEST.EMPLOYEE dataset. You can only specify one SQL in SYSIN.

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=INZUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DSN=TEST.EMPLOYEE,DISP=(NEW,CATLG,DELETE)
//SYSIN    DD *
 SELECT * FROM EMPLOYEE
/*
//

7.4. Environment Configuration

The name and access information of database is specified in SYSTEM:{system} section keys of ikjeft01 subject in OpenFrame configuration.

  1. For more about OpenFrame configuration, refer to OpenFrame Configuration Guide.

  2. In INZUTILB, the libtdbconnsw module is used to connect to the database. Therefore, the libtdbconnsw module must link the library corresponding to the target database.

7.5. Return Codes

The following are return codes returned by INZUTILB from executing batch applications.

  • Upon success:

    It returns the return code from the application.

  • In the case of an error:

    Error messages are printed to SYSPRINT DD and corresponding return codes are returned.

    The following errors may occur in INZUTILB.

    Code Description

    0

    Successful. Returned with an informative message.

    4

    Warning occurred while executing a SQL in the database or result set is missing.

    16

    Occurs when:

    • Database information is missing.

    • SYSREC DD is missing.

    • SYSIN DD is missing.

    • Error occurred while executing a SQL.