Create SQL COBOL Program (CRTSQLCBL)

The Create SQL COBOL Program (CRTSQLCBL) command calls the Structured Query Language (SQL) precompiler which precompiles COBOL source containing SQL statements, produces a temporary source member, and then optionally calls the COBOL compiler to compile the program.

If the Relational database (RDB) parameter is specified and a program is created, an SQL package will be created at the specified relational database.

Parameters

Keyword	Description	Choices	Notes
PGM	Program	Qualified object name	Required, Positional 1
	Qualifier 1: Program	Name
	Qualifier 2: Library	Name, *CURLIB
SRCFILE	Source file	Qualified object name	Optional, Positional 2
	Qualifier 1: Source file	Name, QLBLSRC
	Qualifier 2: Library	Name, *LIBL, *CURLIB
SRCMBR	Source member	Name, *PGM	Optional, Positional 3
COMMIT	Commitment control	*CHG, *ALL, *CS, *NONE, *RR, *UR, *RS, *NC	Optional
RDB	Relational database	Simple name, *LOCAL, *NONE	Optional
TEXT	Text 'description'	Character value, *SRCMBRTXT, *BLANK	Optional
USER	RDB user	Name, *CURRENT	Optional
PASSWORD	RDB user password	Character value, *NONE, ' '	Optional
OPTION	Precompiler options	Values (up to 18 repetitions): *NOSRC, *NOSOURCE, *SRC, *SOURCE, *XREF, *NOXREF, *GEN, *NOGEN, *COMMA, *PERIOD, *JOB, *SYSVAL, *QUOTESQL, *APOSTSQL, *QUOTE, *APOST, *SECLVL, *NOSECLVL, *LSTDBG, *NOLSTDBG, *SQL, *SYS, *NOEXTIND, *EXTIND, *SYSTIME, *NOSYSTIME	Optional
TGTRLS	Target release	Simple name, *CURRENT, *PRV	Optional
INCFILE	INCLUDE file	Qualified object name	Optional
	Qualifier 1: INCLUDE file	Name, *SRCFILE
	Qualifier 2: Library	Name, *LIBL, *CURLIB
ALWCPYDTA	Allow copy of data	*OPTIMIZE, *YES, *NO	Optional
CLOSQLCSR	Close SQL cursor	*ENDPGM, *ENDSQL, *ENDJOB	Optional
ALWBLK	Allow blocking	*ALLREAD, *NONE, *READ	Optional
DLYPRP	Delay PREPARE	*NO, *YES	Optional
CONACC	Concurrent access resolution	*DFT, *CURCMT, *WAIT	Optional
GENLVL	Severity level	0-40, 10	Optional
DATFMT	Date format	*JOB, *USA, *ISO, *EUR, *JIS, *MDY, *DMY, *YMD, *JUL	Optional
DATSEP	Date separator character	*JOB, '/', '.', ',', '-', ' ', *BLANK	Optional
TIMFMT	Time format	*HMS, *USA, *ISO, *EUR, *JIS	Optional
TIMSEP	Time separator character	*JOB, ':', '.', ',', ' ', *BLANK	Optional
REPLACE	Replace	*YES, *NO	Optional
RDBCNNMTH	RDB connect method	*DUW, *RUW	Optional
DFTRDBCOL	Default collection	Name, *NONE	Optional
DYNDFTCOL	Dynamic default collection	*NO, *YES	Optional
SQLPKG	Package	Qualified object name	Optional
	Qualifier 1: Package	Name, *PGM
	Qualifier 2: Library	Name, *PGMLIB
SQLPATH	SQL path	Single values: *NAMING, *LIBL Other values (up to 268 repetitions): Name	Optional
SQLCURRULE	SQL rules	*DB2, *STD	Optional
SAAFLAG	IBM SQL flagging	*NOFLAG, *FLAG	Optional
FLAGSTD	ANS flagging	*NONE, *ANS	Optional
PRTFILE	Print file	Qualified object name	Optional
	Qualifier 1: Print file	Name, QSYSPRT
	Qualifier 2: Library	Name, *LIBL, *CURLIB
USRPRF	User profile	*NAMING, *USER, *OWNER	Optional
DYNUSRPRF	Dynamic user profile	*USER, *OWNER	Optional
SRTSEQ	Sort sequence	Single values: *JOB, *HEX, *JOBRUN, *LANGIDUNQ, *LANGIDSHR Other values: Qualified object name	Optional
	Qualifier 1: Sort sequence	Name
	Qualifier 2: Library	Name, *LIBL, *CURLIB
LANGID	Language id	Character value, *JOB, *JOBRUN	Optional
TOSRCFILE	To source file	Qualified object name	Optional
	Qualifier 1: To source file	Name, QSQLTEMP
	Qualifier 2: Library	Name, QTEMP, *LIBL, *CURLIB
DECRESULT	Decimal result options	Element list	Optional
	Element 1: Maximum precision	31, 63
	Element 2: Maximum scale	0-63, 31
	Element 3: Minimum divide scale	0-9, 0
DECFLTRND	Decimal float rounding mode	*HALFEVEN, *HALFUP, *DOWN, *CEILING, *FLOOR, *HALFDOWN, *UP	Optional
COMPILEOPT	Compiler options	Character value, *NONE	Optional

Program (PGM)

Specifies the program to be created.

This is a required parameter.

Qualifier 1: Program

name

Specify the name of the program to be created.

Qualifier 2: Library

*CURLIB

The current library for the job is used to locate the compiled program. If no current library entry exists in the library list, QGPL is used.

name

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

Source file (SRCFILE)

Specifies the source file that contains the COBOL source statements and SQL statements.

Qualifier 1: Source file

QLBLSRC

Source file QLBLSRC contains the COBOL source.

name

Specify the name of the source file that contains the COBOL source. This source file should have a record length of 92. The source file can be a database file, device file, or an inline data file.

Qualifier 2: Library

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

name

Specify the name of the library to be searched.

Source member (SRCMBR)

Specifies the source file member that contains the input source. This parameter is used only if the source file specified for the Source file (SRCFILE) parameter is a database file.

*PGM

The source file member that has the same name as the program name specified for the Program (PGM) parameter contains the input source.

name

Specify the name of the source file member that contains the input source.

Commitment control (COMMIT)

Specifies whether SQL statements in the compiled program are run under commitment control. Files referred to in the host language source are not affected by this option. Only SQL tables, SQL views, SQL packages, SQL sequences, SQL aliases, SQL Types, SQL procedures, SQL functions, SQL indexes, SQL schemas, SQL triggers, and SQL views referred to in SQL statements are affected.

*CHG or *UR

Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs can be seen.

*CS

Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows updated, deleted, and inserted are locked until the end of the unit of work (transaction). A row that is selected, but not updated, is locked until the next row is selected. Uncommitted changes in other jobs cannot be seen.

*ALL or *RS

Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows selected, updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs cannot be seen.

*NONE or *NC

Specifies that commitment control is not used. Uncommitted changes in other jobs can be seen. If the SQL DROP SCHEMA statement is included in the program, *NONE or *NC must be used. If a relational database is specified for the Relational database (RDB) parameter, and the relational database is on a system that is not on IBM i, *NONE or *NC cannot be specified.

*RR

Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows selected, updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs cannot be seen. All tables referred to in SELECT, UPDATE, DELETE, and INSERT statements are locked exclusively until the end of the unit of work (transaction).

Relational database (RDB)

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

*LOCAL

The program is created as a distributed SQL program. The SQL statements will access the local database. An SQL package object is not created as part of the precompile process. The Create Structured Query Language Package (CRTSQLPKG) command can be used.

*NONE

An SQL package object is not created. The program object is not a distributed program and the Create Structured Query Language Package (CRTSQLPKG) command cannot be used.

name

Specify the name of the relational database where the new SQL package object is to be created. When the name of the local relational database is specified, the program created is still a distributed SQL program. The SQL statements will access the local database.

Text 'description' (TEXT)

Specifies text that briefly describes the program and its function.

*SRCMBRTXT

The text is taken from the source file member being used to create the program. If the source file is an inline file or a device file, the text is blank.

*BLANK

No text is specified.

'description'

Specify no more than 50 characters, enclosed in apostrophes.

RDB user (USER)

Specifies the user name sent to the remote system when starting the conversation. This parameter is valid only when Relational database (RDB) is specified.

*CURRENT

The user name associated with the current job is used.

name

Specify the user name to be used for the application server job.

RDB user password (PASSWORD)

Specifies the password to be used on the remote system. This parameter is valid only when Relational database (RDB) is specified.

*NONE

No password is sent. A user name cannot be specified for the RDB user (USER) parameter if this value is specified.

Note: Specifying a password of a blank is the same as specifying *NONE.

password

Specify the password of the user name specified for the RDB user (USER) parameter.

Precompiler options (OPTION)

Specifies whether the following options are used when the COBOL source is precompiled. If an option is specified more than once, or if two options conflict, the last option specified is used.

Source listing options:

*NOSRC or *NOSOURCE

The precompiler does not produce a source printout unless errors are detected during precompile or create package.

*SRC or *SOURCE

The precompiler produces a source printout.

Cross reference options:

*NOXREF

The precompiler does not produce a cross-reference of names.

*XREF

The precompiler produces a cross-reference between items declared in your program and the numbers of the statements in your program that refer to these items.

Host language compiler call options:

*GEN

The precompiler calls the COBOL compiler. If a relational database name is specified for the Relational database (RDB) parameter, and the compile is successful, an SQL package is also created.

*NOGEN

The precompiler does not call the COBOL compiler. Neither a program nor an SQL package is created.

SQL string delimiter options:

*QUOTESQL

The character used as the string delimiter in the SQL statements is the quote (").

*APOSTSQL

The character used as the string delimiter in the SQL statements is the apostrophe (').

COBOL string delimiter options:

*QUOTE

The character used for nonnumeric literals and Boolean literals in the COBOL statements is the quote (").

*APOST

The character used for nonnumeric literals and Boolean literals in the COBOL statements is the apostrophe (').

Decimal point options:

*JOB

The representation for the decimal point specified for the job at precompile time is used.

Note: If QDECFMT specifies that the value used as the decimal point is a comma, any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.

*SYSVAL

The value used as the decimal point in numeric constants is from the QDECFMT system value. This value is also used as the decimal point character when casting a numeric value to character.

Note: If QDECFMT specifies that the value used as the decimal point is a comma; any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.

*PERIOD

The value used as the decimal point for numeric constants used in SQL statements is a period. This value is also used as the decimal point character when casting a numeric value to character.

*COMMA

The value used as the decimal point in numeric constants is a comma. Any numeric constants in lists (such as in the SELECT clause and VALUES clause) must be separated by a comma followed by a blank. For example, VALUES(1,1, 2,23, 4,1) is equivalent to VALUES(1.1,2.23,4.1) where the decimal point is the period.

Naming convention options:

*SYS

Specifies that the system naming convention is used (library-name/file-name).

*SQL

Specifies that the SQL naming convention is used (schema-name.table-name).

If a relational database is specified for the Relational database (RDB) parameter, and the database is on a system that is not IBM i, *SQL must be specified as the naming convention.

Second-level message text options:

*NOSECLVL

Second-level text descriptions are not added to the listing.

*SECLVL

Second-level text with replacement data is added for all messages on the listing.

Debug listing view options:

*NOLSTDBG

Error and debug information is not generated.

*LSTDBG

The SQL precompiler generates a listing view and error and debug information required for this view.

Note: You can only use *LSTDBG if you are using the CODE product to compile your program.

Extended indicators options:

*NOEXTIND

Extended indicator support is not enabled.

*EXTIND

Extended indicator support is enabled.

System time sensitive options:

*SYSTIME

Specifies that references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

*NOSYSTIME

Specifies that references to system-period temporal tables in both static and dynamic SQL statements are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

Target release (TGTRLS)

Specifies the release of the operating system on which you intend to use the object being created.

When specifying the target-release value, the format VxRxMx is used to specify the release, where Vx is the version, Rx is the release, and Mx is the modification level. For example, V5R3M0 is version 5, release 3, modification 0.

Valid values depend on the current version, release, and modification level of the operating system, and they change with each new release. You can press F4 while prompting this command parameter to see a list of valid target release values.

*CURRENT

The object is to be used on the release of the operating system currently running on your system. The object can also be used on a system with any subsequent release of the operating system installed.

*PRV

The object is to be used on the previous release with modification level 0 of the operating system. The object can also be used on a system with any subsequent release of the operating system installed.

target-release

Specify the release in the format VxRxMx. The object can be used on a system with the specified release or with any subsequent release of the operating system installed.

INCLUDE file (INCFILE)

Specifies the source file that contains members to be included in the program with the SQL INCLUDE statement.

Single values

*SRCFILE

The qualified source file you specify for the Source file (SRCFILE) parameter contains the source file members specified on any SQL INCLUDE statements.

Qualifier 1: INCLUDE file

name

Specify the name of the source file that contains the source file members specified on any SQL INCLUDE statements.

The record length of the source file you specify here must be no less than the record length of the source file you specify for the Source file (SRCFILE) parameter.

Qualifier 2: Library

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

name

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

Allow copy of data (ALWCPYDTA)

Specifies whether a copy of the data can be used in a SELECT statement.

*OPTIMIZE

The system determines whether to use the data retrieved directly from the database or to use a copy of the data. The decision is based on which method provides the best performance. If the Commitment control (COMMIT) parameter is not *NONE, the Allow blocking (ALWBLK) parameter should be set to *ALLREAD, when possible, for best performance.

*YES

A copy of the data is used only when necessary.

*NO

A copy of the data is not used. If a temporary copy of the data is required to perform the query, an error message is returned.

Close SQL cursor (CLOSQLCSR)

Specifies when SQL cursors are implicitly closed, SQL prepared statements are implicitly discarded, and LOCK TABLE locks released. SQL cursors are explicitly closed by issuing the CLOSE, COMMIT (without HOLD), ROLLBACK (without HOLD), or CONNECT (Type 1) SQL statements.

*ENDPGM

The SQL cursors are closed and SQL prepared statements are discarded when the program ends. LOCK TABLE locks are released when the first SQL program on the call stack ends.

*ENDSQL

The SQL cursors remain open between calls and rows can be fetched without running another SQL OPEN statement. One of the programs higher on the call stack must have run at least one SQL statement. The SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the first SQL program on the call stack ends. If you specify *ENDSQL for a program that is the first SQL program called (the first SQL program on the call stack), the program is treated as if *ENDPGM was specified.

*ENDJOB

SQL cursors remain open between calls and can be fetched without running another SQL OPEN statement. The programs higher on the call stack do not need to have run SQL statements. SQL cursors are left open, SQL prepared statements are preserved, and LOCK TABLE locks are held when the first SQL program on the call stack ends. SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the job ends.

Allow blocking (ALWBLK)

Specifies whether the database manager can use record blocking and the extent to which blocking can be used for read-only cursors.

*ALLREAD

Rows are blocked for read-only cursors. All cursors in a program that are not explicitly able to be changed are opened for read-only processing even though there may be EXECUTE or EXECUTE IMMEDIATE statements in the program.

Specifying *ALLREAD:

• Allows record blocking for all read-only cursors.
• Can improve the performance of almost all read-only cursors in programs, but limits queries in the following ways:
  • The Rollback (ROLLBACK) command, a ROLLBACK statement in host languages, or the ROLLBACK HOLD SQL statement does not reposition a read-only cursor when *ALLREAD is specified.
  • Dynamic running of a positioned UPDATE or DELETE statement (for example, using EXECUTE IMMEDIATE), can not be used to update a row in a cursor unless the DECLARE statement for the cursor includes the FOR UPDATE clause.

*NONE

Rows are not blocked for retrieval of data for cursors.

Specifying *NONE:

• Guarantees that the data retrieved is current.
• May reduce the amount of time required to retrieve the first row of data for a query.
• Stops the database manager from retrieving a block of data rows that is not used by the program when only the first few rows of a query are retrieved before the query is closed.
• Can degrade the overall performance of a query that retrieves a large number of rows.

*READ

Records are blocked for read-only retrieval of data for cursors when:

• *NONE is specified for the Commitment control (COMMIT) parameter, which indicates that commitment control is not used.
• The cursor is declared with a FOR READ ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor.

Delay PREPARE (DLYPRP)

Specifies whether the dynamic statement validation for a PREPARE statement is delayed until an OPEN, EXECUTE, or DESCRIBE statement is run. Delaying validation improves performance by eliminating redundant validation.

*NO

Dynamic statement validation is not delayed. When the dynamic statement is prepared, the access plan is validated. When the dynamic statement is used in an OPEN or EXECUTE statement, the access plan is revalidated. Because the authority or the existence of objects referred to by the dynamic statement may change, you must still check the SQLCODE or SQLSTATE after issuing the OPEN or EXECUTE statement to ensure that the dynamic statement is still valid.

*YES

Dynamic statement validation is delayed until the dynamic statement is used in an OPEN, EXECUTE, or DESCRIBE SQL statement. When the dynamic statement is used, the validation is completed and an access plan is built. If you specify *YES for this parameter for precompiled programs, you should check the SQLCODE and SQLSTATE after running an OPEN, EXECUTE, or DESCRIBE statement to ensure that the dynamic statement is valid.

Note: If you specify *YES, performance is not improved if the INTO clause is used on the PREPARE statement or if a DESCRIBE statement uses the dynamic statement before an OPEN is issued for the statement.

Concurrent access resolution (CONACC)

Specifies how the database manager should handle record lock conflicts for data in the process of being updated.

*DFT

Specifies that the concurrent access option will not be explicitly set for this program. The value that is in effect when the program is invoked will be used. The value can be set using the SQL_CONCURRENT_ACCESS_RESOLUTION option in the query options file QAQQINI.

*CURCMT

Directs the database manager to use the currently committed data when possible in the case of record lock conflicts for read-only queries. This only applies when the commit level is *CS.

*WAIT

Directs the database manager to wait for the outcome in the case of record lock conflicts.

Severity level (GENLVL)

Specifies whether the compiler is called, depending on the severity of messages generated as a result of errors found by the SQL precompiler. If precompiler errors are generated that have a message severity level greater than the value specified for this parameter, the compiler is not called.

If the Relational database (RDB) parameter is specified and the severity of the messages generated as a result of package creation is greater than the severity level specified for this parameter, the SQL package is not created.

10

Do not call the compiler if SQL precompiler messages with a message severity greater than 10 are generated.

0-40

Specify the maximum SQL precompiler message severity level to be used to control whether the compiler is called.

Date format (DATFMT)

Specifies the format used when accessing date result columns. All output date fields are returned in the format you specify. For input date strings, the value you specify is used to determine whether the date is a valid format.

Note: An input date string that uses the format *USA, *ISO, *EUR, or *JIS is always valid.

If you connect to a relational database that is on a system that is not IBM i, the format must be *USA, *ISO, *EUR, or *JIS.

*JOB

The format specified for the job at precompile time or when a new interactive SQL session is created is used.

Use the Display Job (DSPJOB) command to determine the current date format for the job.

*USA

The United States date format mm/dd/yyyy is used.

*ISO

The International Organization for Standardization (ISO) date format yyyy-mm-dd is used.

*EUR

The European date format dd.mm.yyyy is used.

*JIS

The Japanese Industrial Standard date format yyyy-mm-dd is used.

*MDY

The date format mm/dd/yy is used.

*DMY

The date format dd/mm/yy is used.

*YMD

The date format yy/mm/dd is used.

*JUL

The Julian date format yy/ddd is used.

Date separator character (DATSEP)

Specifies the separator to be used when accessing date result columns.

Note: This parameter applies only when *JOB, *MDY, *DMY, *YMD, or *JUL is specified for the Date format (DATFMT) parameter.

*JOB

The date separator specified for the job at precompile time, when a new interactive SQL session is created, or when Run SQL Statement (RUNSQLSTM) command is run.

Use the Display Job (DSPJOB) command to determine the current date separator value for the job.

'/'

A slash is used as the date separator.

'.'

A period is used as the date separator.

'-'

A dash is used as the date separator.

','

A comma is used as the date separator.

' ' or *BLANK

A blank is used as the date separator.

Time format (TIMFMT)

Specifies the format used when accessing time result columns. All output time fields are returned in the format you specify. For input time strings, the value you specify is used to determine whether the time is specified in a valid format.

Note: An input time string that uses the format *USA, *ISO, *EUR, or *JIS is always valid.

If you connect to a relational database that is on a system that is not another IBM i, the time format must be *USA, *ISO, *EUR, *JIS, or *HMS with a time separator of a colon or period.

*HMS

The hh:mm:ss format is used.

*USA

The United States time format hh:mmxx is used, where xx is AM or PM.

*ISO

The International Organization for Standardization (ISO) time format hh.mm.ss is used.

*EUR

The European time format hh.mm.ss is used.

*JIS

The Japanese Industrial Standard time format hh:mm:ss is used.

Time separator character (TIMSEP)

Specifies the separator used when accessing time result columns.

Note: This parameter applies only when *HMS is specified for the Time format (TIMFMT) parameter.

*JOB

The time separator specified for the job at precompile time, when a new interactive SQL session is created, or when RUNSQLSTM is run is used.

Use the Display Job (DSPJOB) command to determine the current time separator value for the job.

':'

A colon is used as the time separator.

'.'

A period is used as the time separator.

','

A comma is used as the time separator.

' ' or *BLANK

A blank is used as the time separator.

Replace (REPLACE)

Specifies whether a SQL program or SQL package is created when there is an existing SQL program or SQL package of the same name in the same library. The value is passed to the CRTxxxPGM command (where xxx is the language of the program being created) and the Create SQL Package (CRTSQLPKG) command if the Relational database (RDB) parameter is specified.

*YES

An SQL program or SQL package is created and any existing SQL program or SQL package of the same name in the specified library is moved to QRPLOBJ. The authorities for the existing SQL package are kept for the new SQL package.

*NO

An SQL program or package is not created if an SQL program or package of the same name already exists in the specified library.

RDB connect method (RDBCNNMTH)

Specifies the semantics used for CONNECT statements.

*DUW

CONNECT (Type 2) semantics are used to support distributed unit of work. Consecutive CONNECT statements to additional relational databases do not result in disconnection of previous connections.

*RUW

CONNECT (Type 1) semantics are used to support remote unit of work. Consecutive CONNECT statements result in the previous connection being disconnected before a new connection is established.

Default collection (DFTRDBCOL)

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

*NONE

The naming convention specified for the Precompiler options (OPTION) parameter is used.

name

Specify the name of the schema identifier to be used instead of the naming convention specified for the Precompiler options (OPTION) parameter.

Dynamic default collection (DYNDFTCOL)

Specifies whether the default schema name specified for the Default collection (DFTRDBCOL) parameter is also used for dynamic statements.

*NO

Do not use the value specified for the Default collection (DFTRDBCOL) parameter for unqualified names of tables, views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers for dynamic SQL statements. The naming convention specified for the Precompiler options (OPTION) parameter is used.

*YES

The schema name specified for the Default collection (DFTRDBCOL) parameter will be used for the unqualified names of the tables, views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers in dynamic SQL statements.

Package (SQLPKG)

Specifies the name and library of the SQL package to be created on the remote relational database specified for the Relational database (RDB) parameter of this command.

Qualifier 1: Package

*PGM

The name of the SQL package is the same as the program name.

name

Specify the name of the SQL package. If the remote system is not IBM i, a maximum of 8 characters can be specified.

Qualifier 2: Library

*PGMLIB

The SQL package is placed in the schema that has the same name as the library containing the program.

name

Specify the name of the schema where the SQL package is to be placed. If the remote system is not IBM i, a maximum of 8 characters can be specified.

SQL path (SQLPATH)

Specifies the path to be used to find procedures, functions, and user defined types in static SQL statements.

*NAMING

The path used depends on the naming convention specified for the Precompiler options (OPTION) parameter.

For *SYS naming, the path used is *LIBL, the current library list at runtime.

For *SQL naming, the path used is "QSYS", "QSYS2", "SYSPROC", "SYSIBMADM", "userid", where "userid" is the value of the USER special register. If a schema name is specified for the Default collection (DFTRDBCOL) parameter, the schema name takes the place of userid.

*LIBL

The path used is the library list at runtime.

name

Specify one or more schema names. A maximum of 268 schema names may be specified.

SQL rules (SQLCURRULE)

Specifies the semantics used for SQL statements.

*DB2

The semantics of all SQL statements will default to the rules established for DB2. The following semantics are controlled by this option:

Hexadecimal constants are treated as character data.

Unicode graphic-string constants are UCS-2 (CCSID 13488).

Assignments to SQL-variables and SQL-parameters within the body of a routine or a trigger will use retrieval assignment rules.

When describing a select statement into an SQLDA and SQLN is smaller than the required number of SQLVAR entries, SQLSTATE 01005 is returned only when the result table contains LOBs or UDTs.

*STD

The semantics of all SQL statements will default to the rules established by the ISO and ANSI SQL standards. The following semantics are controlled by this option:

Hexadecimal constants are treated as binary data.

Unicode graphic-string constants are UTF-16 (CCSID 1200).

Assignments to SQL-variables and SQL-parameters within the body of a routine or a trigger will use storage assignment rules.

When describing a select statement into an SQLDA and SQLN is smaller than the required number of SQLVAR entries, SQLSTATE 01005 is always returned.

IBM SQL flagging (SAAFLAG)

Specifies the IBM SQL flagging function. This parameter allows you to flag SQL statements to verify whether they conform to IBM SQL syntax.

*NOFLAG

No checks are made to see whether SQL statements conform to IBM SQL syntax.

*FLAG

Checks are made to see whether SQL statements conform to IBM SQL syntax.

ANS flagging (FLAGSTD)

Specifies whether non-standard statements are flagged. This parameter allows you to flag SQL statements to verify whether they conform to the Core level of the ISO/IEC 9075-2003 standards.

*NONE

No checks are made to see whether SQL statements conform to ANSI standards.

*ANS

Checks are made to see whether SQL statements conform to standards.

Print file (PRTFILE)

Specifies the printer device file to be used for the precompiler output listing.

Qualifier 1: Print file

QSYSPRT

The precompiler output file is directed to the IBM-supplied printer file, QSYSPRT. The file QSYSPRT has a record length of 132. If you specify a file whose record length is less than 132, information is lost.

name

Specify the name of the printer device file to which the precompiler output is directed.

Qualifier 2: Library

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

name

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

User profile (USRPRF)

Specifies the user profile that is used when the compiled program object and SQL package object is run, including the authority that the program object or SQL package has for each object in static SQL statements. The profile of either the owner or the user is used to control access to objects.

*NAMING

The user profile is determined by the naming convention. If the naming convention is *SQL, USRPRF(*OWNER) is used. If the naming convention is *SYS, USRPRF(*USER) is used.

*USER

The profile of the user running the program or SQL package is used.

*OWNER

The user profiles of both the owner and the user are used when the program or SQL package is run.

Dynamic user profile (DYNUSRPRF)

Specifies the user profile used for dynamic SQL statements.

*USER

Local dynamic SQL statements are run under the profile of the program's user. Distributed dynamic SQL statements are run under the profile of the application server job.

*OWNER

Local dynamic SQL statements are run under the profile of the program's owner. Distributed dynamic SQL statements are run under the profile of the SQL package's owner.

Sort sequence (SRTSEQ)

Specifies the sort sequence table to be used for string comparisons in SQL statements.

Note: *HEX must be specified for this parameter on distributed applications where the application server is not on IBM i.

Single values

*JOB

The SRTSEQ value for the job is used.

*JOBRUN

The SRTSEQ value for the job is retrieved when the program is run. For distributed applications, SRTSEQ(*JOBRUN) is valid only when LANGID(*JOBRUN) is also specified.

*LANGIDUNQ

The unique-weight sort table for the language specified for the Language id (LANGID) parameter is used.

*LANGIDSHR

The shared-weight sort table for the language specified for the LANGID parameter is used.

*HEX

A sort sequence table is not used. The hexadecimal values of the characters are used to determine the sort sequence.

Qualifier 1: Sort sequence

name

Specify the name of the sort sequence table to be used with this program.

Qualifier 2: Library

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

name

Specify the name of the library to be searched.

Language id (LANGID)

Specifies the language identifier to be used when SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR) is specified.

*JOB

The LANGID value for the job is retrieved during the precompile.

*JOBRUN

The LANGID value for the job is retrieved when the program is run. For distributed applications, LANGID(*JOBRUN) is valid only when SRTSEQ(*JOBRUN) is also specified.

language-id

Specify the language identifier to be used by the program.

To source file (TOSRCFILE)

Specifies the source file that is to contain the output source member that has been processed by the SQL precompiler. If the specified source file is not found, it will be created. The output member will have the same name as the name specified for the Object (OBJ) or Program (PGM) parameter.

Qualifier 1: To source file

QSQLTEMP

The source file QSQLTEMP will be used.

name

Specify the name of the source file to contain the output source member.

Qualifier 2: Library

QTEMP

The library QTEMP will be used.

*LIBL

The job's library list is searched for the specified file. If the file is not found in any library in the library list, the file will be created in the current library.

*CURLIB

The current library for the job will be used. If no library is specified as the current library for the job, the QGPL library is used.

name

Specify the name of the library that is to contain the output source file.

Decimal result options (DECRESULT)

Specifies the maximum precision, maximum scale, and minimum divide scale that should be used during decimal operations, such as decimal arithmetic. The specified limits only apply to NUMERIC and DECIMAL data types.

Element 1: Maximum precision

31

The maximum precision (length) that should be returned from decimal operations is 31 digits.

63

The maximum precision (length) that should be returned from decimal operations is 63 digits.

Element 2: Maximum scale

31

The maximum scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations is 31 digits.

0-63

Specify the maximum scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations. The value can range from 0 to the maximum precision.

Element 3: Minimum divide scale

0

The minimum divide scale is not used.

0-9

Specify the minimum divide scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations. The value cannot exceed the maximum scale. If 0 is specified for the maximum scale, minimum divide scale is not used.

Decimal float rounding mode (DECFLTRND)

Specifies the decimal floating point rounding mode used for static SQL statements.

*HALFEVEN

Round to nearest; if equidistant, round so that the final digit is even. If the discarded digits represent greater than half (0.5) the value of a one in the next left position then the result coefficient should be incremented by 1 (rounded up). If they represent less than half, then the result coefficient is not adjusted (that is, the discarded digits are ignored). Otherwise (they represent exactly half) the result coefficient is unaltered if its rightmost digit is even, or incremented by 1 (rounded up) if its rightmost digit is odd (to make an even digit).

*HALFUP

Round to nearest; if equidistant, round up. If the discarded digits represent greater than or equal to half (0.5) of the value of a one in the next left position then the result coefficient should be incremented by 1 (rounded up). Otherwise the discarded digits are ignored.

*DOWN

Round towards 0 (truncation). The discarded digits are ignored.

*CEILING

Round towards +infinity. If all of the discarded digits are zero or if the sign is negative the result is unchanged other than the removal of discarded digits. Otherwise, the result coefficient should be incremented by 1 (rounded up).

*FLOOR

Round towards -infinity. If all of the discarded digits are zero or if the sign is positive the result is unchanged other than the removal of discarded digits. Otherwise, the sign is negative and the result coefficient should be incremented by 1.

*HALFDOWN

Round to nearest; if equidistant, round down. If the discarded digits represent greater than half (0.5) of the value of a one in the next left position then the result coefficient should be incremented by 1 (rounded up). Otherwise (the discarded digits are 0.5 or less) the discarded digits are ignored.

*UP

Round away from 0. Of all of the discarded digits are zero the result is unchanged other than the removal of discarded digits. Otherwise, the result coefficient should be incremented by 1 (rounded up).

Compiler options (COMPILEOPT)

Specifies additional parameters to be used on the compiler command. The COMPILEOPT string is added to the compiler command built by the precompiler. There is no validation of the string. The compiler command will issue an error if any parameter is incorrect. Please refer to the DB2 for i SQL programming topic collection in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/ for a list of parameters that the precompiler generates for the compiler command. Using any of the keywords that the precompiler passes to the compiler will cause the compiler command to fail because of duplicate parameters.

*NONE

No additional parameters will be used on the compiler command.

character-value

Specify no more than 5000 characters, enclosed in apostrophes.

Examples

CRTSQLCBL   PGM(ACCTS/STATS)  SRCFILE(ACCTS/ACTIVE)
            TEXT('Statistical analysis of active accounts')

This command runs the SQL precompiler which precompiles the COBOL source and stores the changed source in member STATS of source file QSQLTEMP in library QTEMP. The COBOL compiler is called to create program STATS in library ACCTS using the source member created by the SQL precompiler.

Error messages

*ESCAPE Messages

SQL9001

SQL precompile failed.

SQL9002

Conflict in TGTRLS parameters for SQL precompile and &7 compile.

SQL9003

&7 Compile at wrong level for SQL source.

SQL9004

Create of SQL package failed.

SQL9006

DB2 Query Mgr and SQL DevKit not at same install level as the operating system.