Create SQL Package (CRTSQLPKG)

Where allowed to run: All environments (*ALL)
Threadsafe: No 		Parameters
Examples
Error messages

The Create Structured Query Language Package (CRTSQLPKG) command allows you 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 Relational database (RDB) parameter on a CRTSQLxxx (where xxx = CBL, CBLI, CI, CPPI, PLI, RPG or RPGI) command.

More information is in the SQL Programming information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.

Top

 

Parameters

Keyword Description Choices Notes
PGM Program Qualified object name Required, Positional 1
Qualifier 1: Program Name
Qualifier 2: Library Name, *LIBL, *CURLIB
RDB Relational database Simple name, *PGM Optional, Positional 2
USER RDB user Name, *CURRENT Optional
PASSWORD RDB user password Character value, *NONE, '' Optional
DFTRDBCOL Default collection Name, *PGM, *NONE Optional
OBJTYPE Object type *PGM, *SRVPGM Optional
MODULE Module list Single values: *ALL
Other values (up to 256 repetitions): Name 		Optional
TEXT Text 'description' Character value, *PGMTXT, *BLANK Optional
GENLVL Severity level 0-40, 10 Optional
REPLACE Replace *YES, *NO Optional
PRTFILE Print file Qualified object name Optional
Qualifier 1: Print file Name, QSYSPRT
Qualifier 2: Library Name, *LIBL, *CURLIB

Top

 

Program (PGM)

Specifies the name of the program for which the SQL package is being created. The program must be a distributed SQL program that was created using one of the CRTSQLxxx (where xxx = CBL, CBLI, CI, CPPI, PLI, RPG or RPGI) commands.

Qualifier 1: Program

name

Specify the name of the program for which the SQL package is to be created.

Qualifier 2: Library

*LIBL

All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB

The current library for the job is used to locate the program. If no current library is specified as the current library for the job, QGPL is used.

name

Specify the name of the library where the program is located.

Top

 

Relational database (RDB)

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

*PGM

The relational database name specified for the Relational database (RDB) parameter of the CRTSQLxxx (where xxx = CBL, CBLI, CI, CPPI, PLI, RPG or RPGI) command that originally created the program is used.

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 for this parameter.

Top

 

RDB user (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.

name

Specify the user name being used for the application requester job.

Top

 

RDB user password (PASSWORD)

Specifies the password to be used on the remote system.

*NONE

No password is sent. The user name specified for the RDB user (USER) parameter is not valid if this value is specified.

character-value

Specify the password of the user name specified for the USER parameter. A password value of a blank is treated the same as specifying *NONE.

Top

 

Default collection (DFTRDBCOL)

Specifies the schema name to be used for unqualified names of tables, views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers. This parameter applies only to static SQL statements in the package.

*PGM

The schema name specified for the Default collection (DFTRDBCOL) parameter of the CRTSQLxxx (where xxx = CBL, CBLI, CI, CPPI, PLI, RPG or RPGI) command used to create the program for which an SQL package is being created is used.

*NONE

The Naming convention option specified for the Precompiler options (OPTION) parameter of the CRTSQLxxx (where xxx = CBL, CBLI, CI, CPPI, PLI, RPG or RPGI) command used to precompile the program is used to determine the schema name.

name

Specify the schema name that is used for unqualified tables, views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers.

Top

 

Object type (OBJTYPE)

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

*PGM

Create an SQL package from the program specified for the Program (PGM) parameter.

*SRVPGM

Create an SQL package from the service program specified for the PGM parameter.

Top

 

Module list (MODULE)

Specifies a list of modules in a bound program.

Single values

*ALL

An SQL package is created for all modules 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 program.

Other values (up to 256 repetitions)

name

Specify the modules in the program for which an SQL package is to be created. If more than 256 modules exist that need to be packaged, multiple CRTSQLPKG commands must be used. A maximum of 1024 modules can be in a program that has at least one module containing an SQL statement.

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 for the MODULE parameter, processing continues to determine whether an SQL package should be created. If the module is created using SQL and the Relational database (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.

Top

 

Text 'description' (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 no more than 50 characters of text, enclosed in apostrophes.

Top

 

Severity level (GENLVL)

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

10

The maximum severity level is 10.

0-40

Specify the maximum severity level.

Top

 

Replace (REPLACE)

Specifies whether an existing SQL package of the same name in the specified library is replaced by the new SQL package.

*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.

Top

 

Print file (PRTFILE)

Specifies 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.

Qualifier 1: Print file

QSYSPRT

The create SQL package error listing is directed to the IBM-supplied printer file, QSYSPRT.

name

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

Qualifier 2: Library

*LIBL

All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB

The current library for the job is used to locate the printer file. If no library is specified as the current library for the job, QGPL is used.

name

Specify the name of the library where the printer file is located.

Top

 

Examples 

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

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

Top

 

Error messages

*ESCAPE Messages

SQL9004

Create of SQL package failed.

SQL9006

DB2 UDB Query Manager and SQL Development Kit for iSeries not at same install level as i5/OS.

Top