Start SQL Interactive Session (STRSQL)

The Start Structured Query Language (STRSQL) command starts the interactive SQL program. The program starts the statement entry of the interactive SQL program which immediately shows the Enter SQL Statements display. This display allows you to build, edit, enter, and run a SQL statement in an interactive environment. Any messages during the running of the program are shown on this display.

Parameters

Keyword Description Choices Notes
COMMIT Commitment control *NONE, *CHG, *CS, *ALL, *RR, *NC, *UR, *RS Optional, Positional 1
NAMING Naming convention *SYS, *SQL Optional, Positional 2
PROCESS Statement processing *RUN, *VLD, *SYN Optional, Positional 3
LIBOPT Library option Name, *LIBL, *USRLIBL, *ALLUSR, *ALL, *CURLIB Optional, Positional 4
LISTTYPE List type *ALL, *SQL Optional, Positional 5
REFRESH Data refresh *ALWAYS, *FORWARD Optional, Positional 6
ALWCPYDTA Allow copy data *YES, *OPTIMIZE, *NO 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
DECPNT Decimal point *JOB, *PERIOD, *COMMA, *SYSVAL Optional
SRTSEQ Sort sequence Single values: *HEX, *JOB, *JOBRUN, *LANGIDUNQ, *LANGIDSHR
Other values: Qualified object name
Optional
Qualifier 1: Sort sequence Name
Qualifier 2: Library Name, *LIBL, *CURLIB
LANGID Language identifier Character value, *JOB, *JOBRUN Optional
PGMLNG Program language *NONE, *C, *CBL, *PLI, *RPG, *FTN Optional
SQLSTRDLM SQL string delimiter *QUOTESQL, *APOSTSQL Optional

Commitment control (COMMIT)

Specifies whether SQL statements are run under commitment control.

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

Naming convention (NAMING)

Specifies the naming convention used for objects in SQL statements.

*SYS
The system naming convention (library-name/file-name) is used.
*SQL
The SQL naming convention (schema-name.table-name) is used.

Statement processing (PROCESS)

Specifies what values are used to process the statements.

*RUN
The statements are syntax checked, data checked, and then run.
*VLD
The statements are syntax checked and data checked but not run.
*SYN
The statements are syntax checked only.

Library option (LIBOPT)

Specifies the list of libraries that are shown when you request list support from the Enter SQL Statements display (F4, F16, F17, F18). If you do not have *EXECUTE authority for a specified library, that library is not shown.

*LIBL
All libraries in the job's library list are searched until the first match is found.
*CURLIB
The job's current library is shown.
*USRLIBL
Only the libraries in the user portion of the job's library list are shown.
*ALL
All of the libraries in the system, including QSYS, are shown.
*ALLUSR
All user libraries are searched. All libraries with names that do not begin with the letter Q are searched except for the following:

#CGULIB     #DSULIB     #SEULIB
#COBLIB     #RPGLIB
#DFULIB     #SDALIB

Although the following Qxxx libraries are provided by IBM, they typically contain user data that changes frequently. Therefore, these libraries are considered "user libraries", and are also searched:

QDSNX        QRCL         QUSRSYS
QGPL         QS36F        QUSRVxRxMx
QGPL38       QUSER38
QPFRDATA     QUSRINFSKR

Note: A different library name, of the form QUSRVxRxMx, can be created by the user for each release. VxRxMx is the version, release, and modification level of the library.

name
Specify the name of the library to be shown.

List type (LISTTYPE)

Specifies what types of objects are displayed when you request list support from the Enter SQL Statements display (F16, F17, and F18) or from a prompt (F4) display.

*ALL
All objects are displayed.
*SQL
Only SQL created objects are displayed.

Data refresh (REFRESH)

Specifies when the display select output data is refreshed. This parameter does not apply to any SQL SELECT statements that require the creation of a temporary result.

*ALWAYS
The data is always refreshed. You cannot specify *ALWAYS for this parameter and *OPTIMIZE for the Allow copy of data (ALWCPYDTA) parameter.
*FORWARD
The data is refreshed only as the user pages forward to the end of the data for the first time. After that, it is not refreshed.

Allow copy of data (ALWCPYDTA)

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

*YES
A copy of the data can only be used, if necessary, to run a SELECT statement.
*OPTIMIZE
The system chooses whether to use the data retrieved directly from the database or to use a copy of the data. The decision is based on which choice provides the best performance.

This value decreases the time required for the total query. Since the copy of the data must be made before returning the first row of the result table, the time to retrieve the first row may be increased.

*NO
A copy of the data is not allowed. If the clauses in the SELECT statement require a copy of the data, an error message is returned. If the SELECT statement runs successfully, current data was used.

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.

Decimal Point (DECMPT)

Specifies the decimal point character to be used for numeric constants in SQL statements. This value is also used as the decimal point character when casting between character and numeric values.

*JOB
The representation for the decimal point is the value used by the job running the statement.
*SYSVAL
The QDECFMT system value is used as the decimal point character.
*PERIOD
A period represents the decimal point.
*COMMA
A comma represents the decimal point.

Sort sequence (SRTSEQ)

Specifies the sort sequence table to be used for string comparisons in SQL statements on the Enter SQL Statements display.

Single values

*JOB
The SRTSEQ value is retrieved when the user starts interactive SQL.
*JOBRUN
The SRTSEQ value for the job is retrieved each time the user starts interactive SQL.
*LANGIDUNQ
A unique-weight sort table is used.
*LANGIDSHR
A shared-weight sort table is used.
*HEX
A sort sequence table is not used, and 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 the interactive SQL session.

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 identifier (LANGID)

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

*JOB
The LANGID value specified for the job is used when the user starts a new interactive SQL.
*JOBRUN
The LANGID value for the job is retrieved each time the user starts interactive SQL.
language-ID
Specify the language identifier to be used.

Program language (PGMLNG)

Specifies the program language syntax rules to use. To use this parameter, you must specify *SYN for the Statement processing (PROCESS) parameter.

*NONE
You are not using a specific language's syntax check rules.
*C
You are checking syntax using the C language syntax rules.
*CBL
You are checking syntax using the COBOL language syntax rules.
*PLI
You are checking syntax using the PL/I language syntax rules.
*RPG
You are checking syntax using the RPG language syntax rules.
*FTN
You are checking syntax using the FORTRAN language syntax rules.

SQL string delimiter (SQLSTRDLM)

Specifies the SQL string delimiter if *CBL is specified for the Program language (PGMLNG) parameter.

*QUOTESQL
The SQL string delimiter is represented by the quotation mark (").
*APOSTSQL
The SQL string delimiter is represented by the apostrophe (').

Examples

STRSQL

This command starts the Interactive SQL program. The program starts the statement entry of the interactive SQL program which immediately shows the Enter SQL Statements display. This display allows you to build, edit, enter, and run a SQL statement in an interactive environment.

Error messages

*ESCAPE Messages

CPF9801
Object &2 in library &3 not found.
CPF9802
Not authorized to object &2 in &3.
CPF9810
Library &1 not found.
CPF9820
Not authorized to use library &1.
CPF9822
Not authorized to file &1 in library &2.
CPF9830
Cannot assign library &1.
SQL0113
Name &1 not allowed.
SQL6141
Cannot use interactive SQL now.
SQL6145
Interactive SQL session has exceeded system limit.
SQL6332
Authorization list &1 does not exist.
SQL6342
Sort sequence &1 is not valid.
SQL6343
Language identifier &1 is not valid.
SQL9006
DB2 Query Mgr and SQL DevKit not at same install level as the operating system.
SQL9012
IBM DB2 Query Manager and SQL Development Kit for i not available.