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 DB2 for i SQL programming topic collection in the IBM i Information Center at http://www.ibm.com/systems/i/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 Query Mgr and SQL DevKit not at same install level as the operating system.
| Top |