Start SQL Interactive Session (STRSQL)

Where allowed to run: Interactive environments (*INTERACT *IPGM *IREXX *EXEC)
Threadsafe: No

Parameters
Examples
Error messages

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.