Create SQL FORTRAN Program (CRTSQLFTN)
The Create SQL FORTRAN Program (CRTSQLFTN) command calls the
Structured Query Language (SQL) precompiler which precompiles
FORTRAN source containing SQL statements and produces a temporary
source member.
Note: An IBM FORTRAN compiler is no longer supported.
This CRTSQLFTN command is provided for use by other non-IBM FORTRAN
compilers.
If the Relational database (RDB) parameter is specified
and a program is created, an SQL package will be created at the
specified relational database.
| 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, QFTNSRC |
| 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 12 repetitions):
*NOSRC, *NOSOURCE, *SRC, *SOURCE, *XREF, *NOXREF,
*NOGEN, *SQL, *SYS, *JOB, *SYSVAL, *PERIOD, *COMMA, *SECLVL,
*NOSECLVL, *DEBUG, *NODEBUG |
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 |
| 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 |
| 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 FORTRAN source
statements and SQL statements.
Qualifier 1: Source file
- QFTNSRC
- Source file QFTNSRC contains the FORTRAN source.
- name
- Specify the name of the source file that contains the FORTRAN
source.
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,
and SQL packages 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 an iSeries system, *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 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 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 USER
parameter.
Precompiler options (OPTION)
Specifies whether the following options are used when the
FORTRAN 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:
- *NOGEN
- The precompiler does not call the FORTRAN compiler. Neither a
program nor an SQL package is created.
Decimal point options:
- *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.
- *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 or the 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) in which the decimal point is a
period.
- *SYSVAL
- The value used as the decimal point is the QDECFMT system
value.
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 or the 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) in which the decimal point is a
period.
- *COMMA
- The value used as the decimal point is a comma.
Note: Any numeric constants in lists (such as in the
SELECT clause or the 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 a
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 an iSeries system, *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 information options:
- *NODEBUG
- Symbolic extended program model (EPM) debug information is not
stored with the program. This option is passed to the compiler and
does not affect the SQL precompiler.
- *DEBUG
- Symbolic EPM debug information is stored with the program. This
option is passed to the compiler and does not affect the SQL
precompiler.
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.
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 an iSeries system, 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 iSeries system, 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 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 OPTION parameter.
Dynamic default collection (DYNDFTCOL)
Specifies whether the default schema name specified for the
DFTRDBCOL parameter is also used for dynamic statments.
- *NO
- Do not use the value specified for the 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 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 an iSeries system, 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 an iSeries system, 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", "userid",
where "userid" is the value of the USER special register. If a
schema name is specified for the 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.
- *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.
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 an
iSeries system.
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 SRCMBR
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 returned for result data types. The
specified limit only applies to numeric (zoned) and decimal
(packed) data types used in arithmetic expressions and in SQL
column functions AVG and SUM.
Element 1: Maximum precision
- 31
- The maximum precision (length) that should be returned for the
result data types is 31 digits.
- 63
- The maximum precision (length) that should be returned for the
result data types 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 for the result data
types is 31 digits.
- 0-63
- Specify the maximum scale (number of decimal positions to the
right of the decimal point) that should be returned for the result
data types. The value can range from 0 to the maximum
precision.
Element 3: Minimum divide scale
- 0
- The minimum divide scale (number of decimal positions to the
right of the decimal point) that should be returned for both
intermediate and result data types is 0.
- 0-9
- Specify the minimum divide scale (number of decimal positions
to the right of the decimal point) that should be returned for both
intermediate and result data types. The value cannot to exceed the
maximum scale. If 0 is specified for the maximum scale, minimum
divide scale is not used.
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 SQL Programming information in the iSeries
Information Center at http://www.ibm.com/eserver/iseries/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.
CRTSQLFTN PGM(PAYROLL) TEXT('Payroll Program')
This command runs the SQL precompiler, which precompiles the
FORTRAN source and stores the changed source in member PAYROLL of
source file QSQLTEMP in library QTEMP.
*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 UDB Query Manager and SQL Development Kit for iSeries not
at same install level as i5/OS.