Create SQL Package (CRTSQLPKG) command

You do not need the DB2/400 Query Manager and SQL Development Kit licensed program to create an SQL package on an AS.

You can enter the CRTSQLPKG command to create an SQL package from a compiled distributed relational database program. You can also use this command to replace an SQL package that was created previously. A new SQL package is created on the relational database defined by the RDB parameter. The new SQL package has the same name and is placed in the same library as specified on the PKG parameter of the CRTSQLxxx command.




Job: B,I Pgm: B,I REXX: B,I Exec
                     .-*LIBL/--------.
>>-CRTSQLPKG---PGM(--+---------------+---program-name--)-------->
                     +-*CURLIB/------+
                     '-library-name/-'
 
>-----+-----------------------------------------+--------------->
      |       .-*PGM---------------------.      |
      '-RDB(--+-relational-database-name-+---)--'
 
>-----+---------------------------+----------------------------->
      |        .-*CURRENT--.      |
      '-USER(--+-user-name-+---)--'
 
>-----+------------------------------+-------------------------->
      |            .-*NONE----.      |
      '-PASSWORD(--+-password-+---)--'
 
>-----+----------------------------------+---------------------->
      |          .-10-------------.      |
      '-GENLVL(--+-severity-level-+---)--'
 
>-----+--------------------------+------------------------------>
      |           .-*YES--.      |
      '-REPLACE(--+-*NO---+---)--'
 
>-----+--------------------------------------+------------------>
      |             .-*PGM------------.      |
      '-DFTRDBCOL(--+-*NONE-----------+---)--'
                    '-collection-name-'
 
>-----+----------------------------------------------------------+>
      |           .-*LIBL/--------.   .-QSYSPRT-----------.      |
      '-PRTFILE(--+---------------+---+-printer-file-name-+---)--'
                  +-*CURLIB/------+
                  '-library-name/-'
 
>-----+----------------------------+---------------------------->
      |           .-*PGM----.      |
      '-OBJTYPE(--+-*SRVPGM-+---)--'
 
>-----+-------------------------------------------+------------->
      |          .-*ALL--------------------.      |
      |          |  .-.------------------. |      |
      |          |  V              (1)   | |      |
      '-MODULE(--+----module-name--------+-+---)--'
 
>-----+-------------------------------+------------------------><
      |        .-*PGMTXT-------.      |
      '-TEXT(--+-*BLANK--------+---)--'
               '-'description'-'


Notes:


  1. A maximum of 256 modules may be specified.


PGM
Specifies the qualified name of the program for which the SQL package is being created.

*LIBL: Specifies that the library list is used to locate the program.

*CURLIB: Specifies that the current library is able to find the program. If a current library entry does not exist in the library list, the QGPL library is used.

library-name: Specifies the library where the program is located.

program-name: Specifies the name of the distributed program for which the SQL package is being created.

RDB
Specifies the relational database name that identifies

the remote database where the SQL package is being created.

*PGM: Specifies that the relational database name to be used is the same as the value specified on the RDB parameter of the CRTSQLxxx command used when the program was created.

relational-database-name: Specifies the name of the relational database where the SQL package is to be created.

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: Specifies the user name to be used for the remote job.

PASSWORD
Specifies the password to be used on the remote system.

*NONE: No password is sent. If a user name is specified on the USER parameter, the value is not valid.

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

GENLVL
Controls the generation of the SQL package. If error messages are returned with a severity greater than the GENLVL value, the SQL package is not created.

10: If a severity level value is not specified, the default severity level is 10.

severity-level: Specify a number from 0 through 40. Some suggested values are listed below:

10
warnings

20
general error messages

30
serious error messages

40
system detected error messages
Note:There are some errors that cannot be controlled by GENLVL. When those errors occur, the SQL package is not created.

REPLACE
Specifies whether or not to replace an existing SQL package of the same name with a newly created SQL package.

*YES: Specifies that if the SQL package already exists, it will be replaced with the new SQL package.

*NO: Specifies that the create SQL package operation will end if an SQL package already exists.

DFTRDBCOL
Identifies the default collection name to be used for unqualified names of tables, views, indexes and SQL packages with static SQL statements.

*PGM: Specifies that the collection name to be used is the same as the DFTRDBCOL parameter value used when the program was created.

*NONE: Specifies that unqualified names for tables, indexes, views, and SQL packages will use the search conventions defined for the *SQL and *SYS options in the SQL precompiler commands.

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

PRTFILE
Specifies the qualified name of the printer device file to which the precompiler listing is directed. The file should have a minimum length of 132 characters. If a file with a record length of less than 132 characters is specified, information is lost.

*LIBL: Specifies the library list used to locate the printer file.

*CURLIB: Specifies that the current library for the job is used to locate the printer file. If no library entry exists in the library list, QGPL is used.

library-name: Specify the library where the printer file is located.

QSYSPRT: If a file name is not specified, the precompiler listing is directed to the IBM-supplied printer file QSYSPRT.

printer-file-name: Specify the name of the printer device file to which the precompiler 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 program and its function.

*PGMTXT: Specifies that the text is taken from the program.

*BLANK: Specifies no text.

'description': Specify no more than 50 characters of text enclosed in apostrophes (').

The following sample command creates an SQL package from the distributed SQL program INVENT on relational database KC000. 

CRTSQLPKG  INVENT  RDB(KC000)   TEXT('Inventory Check')

The new SQL package is created with the same options that were specified on the CRTSQLxxx command.

If errors are encountered while creating the SQL package, the SQL statement being processed when the error occurred and the message text for the error are written to the file identified by the PRTFILE parameter. A listing is not generated if no errors were found during the create SQL package process.

If the CRTSQLxxx command failed to create an SQL package (for example, the communications line failed during the precompile) but the program was created, the SQL package can be created without running the CRTSQLxxx command again.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]