CRTSQLPKG (Create Structured Query Language Package) Command Description

CRTSQLPKG Command syntax diagram

 

Purpose

The Create Structured Query Language Package (CRTSQLPKG) command is used to create (or re-create) an SQL package on a relational database from an existing distributed SQL program. A distributed SQL program is a program created by specifying the RDB parameter on a CRTSQLxxx (where xxx = C, CI, CPPI, CBL, CBLI, FTN, PLI, or RPG or RPGI) command.

 

Required Parameters

PGM

Specifies the qualified name of the program for which the SQL package is being created. The program must be a distributed SQL program.

The name of the program can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list are searched until the first match is found.

*CURLIB: The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

library-name: Specify the name of the library to be searched.

program-name: Specify the name of the program for which the package is being created.

 

Optional Parameters

RDB

Specifies the name of the relational database where the SQL package is being created.

*PGM: The relational database name specified for the SQL program is used. The relational database name is specified on the RDB parameter of the distributed SQL program.

relational-database-name: Specify the name of the relational database where the SQL package is to be created. Use the Work with Relational Database Directory Entry (WRKRDBDIRE) command to show the relational database names that are valid on this parameter.

USER

Specifies the user name sent to the remote system when starting the conversation.

*CURRENT: The user name associated with the current job is used.

user-name: Specify the user name being used for the application server job.

PASSWORD

Specifies the password to be used on the remote system.

*NONE: No password is sent. If this value is specified, USER(*CURRENT) must also be specified.

password: Specify the password of the user name specified on the USER parameter.

GENLVL

Specifies the maximum severity level allowed for errors detected during SQL package creation. If errors occur at a level that exceeds the specified level, the SQL package is not created.

10: The default severity-level is 10.

severity-level: Specify the maximum severity level. Valid values range from 0 through 40.

REPLACE

Specifies whether an existing package is being replaced with the new package. More information on this parameter is in Commonly used parameters.

*YES: An existing SQL package of the same name is replaced by the new SQL package.

*NO: An existing SQL package of the same name is not replaced; a new SQL package is not created if the package already exists in the specified library.

DFTRDBCOL

Specifies the collection name to be used for unqualified names of tables, views, indexes, and SQL packages. This parameter applies only to static SQL statements in the package.

*PGM: The collection name specified for the SQL program is used. The default relational database collection name is specified on the DFTRDBCOL parameter of the distributed SQL program.

*NONE: Unqualified names for tables, views, indexes, and SQL packages use the search conventions specified on the OPTION parameter of the CRTSQLxxx command used to create the program.

collection-name: Specify the collection name that is used for unqualified tables, views, indexes, and SQL packages.

PRTFILE

Specifies the qualified name of the printer device file to which the create SQL package error listing is directed. If no errors are detected during the creation of the SQL package, no listing is produced.

The name of the printer file can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list are searched until the first match is found.

*CURLIB: The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

library-name: Specify the name of the library to be searched.

QSYSPRT: If a file name is not specified, the create SQL package error listing is directed to the IBM-supplied printer file QSYSPRT.

printer-file-name: Specify the name of the printer device file to which the create SQL package error listing is directed.

OBJTYPE

Specifies the type of program for which an SQL package is created.

*PGM: Create an SQL package from the program specified on the PGM parameter.

*SRVPGM: Create an SQL package from the service program specified on the PGM parameter.

MODULE

Specifies a list of modules in a bound program.

*ALL: An SQL package is created for each module in the program. An error message is sent if none of the modules in the program contain SQL statements or none of the modules is a distributed module.

Note:

CRTSQLPKG can process programs that do not contain more than 1024 modules.

module-name: Specify the names of up to 256 modules in the program for which an SQL package is to be created. If more than 256 modules exist that need to have an SQL package created, multiple CRTSQLPKG commands must be used.

Duplicate module names in the same program are allowed. This command looks at each module in the program and if *ALL or the module name is specified on the MODULE parameter, processing continues to determine whether an SQL package should be created. If the module is created using SQL and the RDB parameter is specified on the precompile command, an SQL package is created for the module. The SQL package is associated with the module of the bound program.

TEXT

Specifies text that briefly describes the SQL package and its function.

*PGMTXT: The text from the program for which the SQL package is being created is used.

*BLANK: No text is specified.

'description': Specify a maximum of 50 characters of text, enclosed in apostrophes.

Example for CRTSQLPKG

CRTSQLPKG   PAYROLL  RDB(SYSTEMA)
  TEXT('Payroll Program')

This command creates an SQL package from the distributed SQL program PAYROLL on relational database SYSTEMA.

Error messages for CRTSQLPKG

*ESCAPE Messages

SQL9004

Create of SQL package failed.

SQL9006

DB2 Query Manager and SQL Development Kit for AS/400 not at same install level as OS/400.