Start SQL Interactive Session (STRSQL)

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.

Top

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

Top

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

Top

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.

Top

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.

Top

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

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.

Top

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.

Top

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.

Top

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.

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.

Top

Date separator character (DATSEP)

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

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.

Top

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.

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.

Top

Time separator character (TIMSEP)

Specifies the separator used when accessing time result columns.

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.

Top

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.

Top

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.

Top

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.

Top

Program language (PGMLNG)

Specifies the program language syntax rules to use. To use this parameter, 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.

Top

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 (').

Top

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.

Top

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 UDB Query Manager and SQL Development Kit for iSeries not at same install level as i5/OS.

SQL9012

DB2 UDB Query Manager and SQL Development Kit for iSeries not available.

Top

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