PDA

View Full Version : Embedded SQL - Datenbankoptionen in VARPG



woki
13-04-04, 11:18
Hallo,

ich bin absoluter VARPG-Neuling und versuche mich gerade an meinem ersten Programm.
Hier bin ich auf den ersten Fehler gestoßen:


C/EXEC SQL
RNV8501E Eingebettete SQL-Anweisungen sind nur zulässig, wenn die Datenbankoption angegeben wurde; die Anweisung wird ignoriert.
C+ Select PROGAK, PNAMAK, KDN5AK
C+ Into :PNUM, :PNAM, :KNUM
C+ From ABKOPP
C+ Where PROGAK = '016'
C+ Group by PROGAK
C/END-EXEC

woki
13-04-04, 12:41
Hat es vielleicht hiermit was zu tun?


SET OPTION

The SET OPTION statement establishes the processing options to be used for SQL statements.


Invocation
This statement can be used in a REXX procedure or embedded in an application program. If used in a REXX procedure, it is an executable statement. If embedded in an application program, it is not executable and must precede any other SQL statements. This statement cannot be dynamically prepared.


Authorization
None required.


Syntax
.-,--------------------------------------.
V |
>>-SET OPTION------+-ALWBLK = --alwblk-option----------+--+----><
+-ALWCPYDTA = --alwcpydta-option----+
+-CLOSQLCSR = --closqlcsr-option----+
+-CNULRQD = --cnulrqd-option--------+
+-COMMIT = --commit-option----------+
+-DATFMT = --datfmt-option----------+
+-DATSEP = --datsep-option----------+
+-DECMPT = --decmpt-option----------+
+-DFTRDBCOL = --dftrdbcol-option----+
+-DLYPRP = --dlyprp-option----------+
+-DYNDFTCOL = --dyndftcol-option----+
+-DYNUSRPRF = --dynusrprf-option----+
+-LANGID = --langid-option----------+
+-NAMING = --naming-option----------+
+-OPTLOB = --optlob-option----------+
+-RDBCNNMTH = --rdbcnnmth-option----+
+-SQLCURRULE = --sqlcurrule-option--+
+-SQLPATH = --sqlpath-option--------+
+-SRTSEQ = --srtseq-option----------+
+-TIMFMT = --timfmt-option----------+
+-TIMSEP = --timsep-option----------+
'-USRPRF = --usrprf-option----------'



alwblk-option

|--+-*READ----+-------------------------------------------------|
+-*NONE----+
'-*ALLREAD-'

alwcpydta-option

|---+-*YES------+-----------------------------------------------|
+-*NO-------+
'-*OPTIMIZE-'

closqlcsr-option

|---+-*ENDACTGRP-+----------------------------------------------|
+-*ENDMOD----+
+-*ENDPGM----+
+-*ENDSQL----+
'-*ENDJOB----'

cnulrqd-option

|---+-*YES-+----------------------------------------------------|
'-*NO--'

commit-option

(1)
|---+-*CHG-------+----------------------------------------------|
| (2) |
+-*NONE------+
+-*CS--------+
| (3) |
+-*ALL-------+
'-*RR--------'



Notes:


*UR can be used as a synonym for *CHG.

*NC can be used as a synonym for *NONE.

*RS can be used as a synonym for *ALL.


datfmt-option

|--+-*JOB-+-----------------------------------------------------|
+-*ISO-+
+-*EUR-+
+-*USA-+
+-*JIS-+
+-*MDY-+
+-*DMY-+
+-*YMD-+
'-*JUL-'

datsep-option

|---+-*JOB----+-------------------------------------------------|
+-*SLASH--+
+-'/'-----+
+-*PERIOD-+
+-'.'-----+
+-*COMMA--+
+-','-----+
+-*DASH---+
+-'-'-----+
+-*BLANK--+
'-' '-----'

decmpt-option

|---+-*PERIOD-+-------------------------------------------------|
+-*COMMA--+
+-*SYSVAL-+
'-*JOB----'

dftrdbcol-option

|---+-*NONE-----------+-----------------------------------------|
'-collection-name-'

dlyprp-option

|---+-*YES-+----------------------------------------------------|
'-*NO--'

dyndftcol-option

|---+-*YES-+----------------------------------------------------|
'-*NO--'

dynusrprf-option

|---+-*OWNER-+--------------------------------------------------|
'-*USER--'

langid-option

|---+-*JOB--------+---------------------------------------------|
+-*JOBRUN-----+
'-language-ID-'



naming-option

|--+-*SYS-+-----------------------------------------------------|
'-*SQL-'

optlob-option

|---+-*YES-+----------------------------------------------------|
'-*NO--'

rdbcnnmth-option

|---+-*DUW-+----------------------------------------------------|
'-*RUW-'

sqlcurrule-option

|---+-*DB2-+----------------------------------------------------|
'-*STD-'

sqlpath-option

|---+-*LIBL----------------+------------------------------------|
'-path-string-constant-'

srtseq-option

|---+-*JOB-----------------------------------+------------------|
+-*HEX-----------------------------------+
+-*JOBRUN--------------------------------+
+-*LANGIDUNQ-----------------------------+
+-*LANGIDSHR-----------------------------+
| .-*LIBL/--------. |
'-+---------------+---srtseq-table-name--'
+-*CURLIB/------+
'-library-name/-'

timfmt-option

|---+-*HMS-+----------------------------------------------------|
+-*ISO-+
+-*EUR-+
+-*USA-+
'-*JIS-'

timsep-option

|---+-*JOB----+-------------------------------------------------|
+-*COLON--+
+-':'------+
+-*PERIOD-+
+-'.'-----+
+-*COMMA--+
+-','-----+
+-*BLANK--+
'-' '-----'

usrprf-option

|---+-*OWNER--+-------------------------------------------------|
+-*USER---+
'-*NAMING-'



Description

ALWBLK
Specifies whether the database manager can use record blocking and the extent to which blocking can be used for read-only cursors. This option will be ignored in REXX.

*ALLREAD
Rows are blocked for read-only cursors if COMMIT is *NONE or *CHG. All cursors in a program that are not explicitly able to be updated are opened for read-only processing even though EXECUTE or EXECUTE IMMEDIATE statements may be in the program.
Specifying *ALLREAD:

Allows record blocking under commitment control level *CHG in addition to the blocking allowed for *READ.
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), cannot 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 on the 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.
Specifying *READ can improve the overall performance of queries that meet the above conditions and retrieve a large number of records.


ALWCPYDTA
Specifies whether a copy of the data can be used in a SELECT statement. This option will be ignored in REXX.

*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 COMMIT is *CHG or *CS and ALWBLK in not *ALLREAD, or if COMMIT is *ALL or *RR, then a copy of the data is used only when it is necessary to run a query.

*YES
A copy of the data is used only when necessary.

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

CLOSQLCSR
Specifies when SQL cursors are implicitly closed, SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released. SQL cursors are explicitly closed when you issue the CLOSE, COMMIT, or ROLLBACK (without HOLD) SQL statements. This option will be ignored in REXX. *ENDACTGRP and *ENDMOD are for use by ILE programs and modules. *ENDPGM, *ENDSQL, and *ENDJOB are for use by non-ILE programs.

*ENDACTGRP
SQL cursors are closed, SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released when the activation group ends.

*ENDMOD
SQL cursors are closed and SQL prepared statements are implicitly discarded when the module is exited. LOCK TABLE locks are released when the first SQL program on the call stack ends.

*ENDPGM
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
SQL cursors remain open between calls and can be fetched without running another SQL OPEN. One of the programs higher on the call stack must have run at least one SQL statement. 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 *ENDSQL is specified 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. 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.

CNULRQD
Specifies whether a NUL-terminator is returned for character and graphic host variables. This option will only be used for SQL statements in C and C++ programs.

*YES
Output character and graphic host variables always contain the NUL-terminator. If there is not enough space for the NUL-terminator, the data is truncated and the NUL-terminator is added. Input character and graphic host variables require a NUL-terminator.

*NO
For output character and graphic host variables, the NUL-terminator is not returned when the host variable is exactly the same length as the data. Input character and graphic host variables do not require a NUL-terminator.

COMMIT
Specifies the isolation level to be used. In REXX, files that are referred to in the source are not affected by this option. Only tables, views, and packages referred to in SQL statements are affected. For more information on isolation levels, see "Isolation Level"

*CHG
Specifies the isolation level of Uncommitted Read.


*NONE
Specifies the isolation level of No Commit. If the DROP COLLECTION statement is included in a REXX procedure, *NONE must be used.


*CS
Specifies the isolation level of Cursor Stability.


*ALL
Specifies the isolation level of Read Stability.


*RR
Specifies the isolation level of Repeatable Read.

DATFMT
Specifies the format used when accessing date result columns. All output date fields are returned in the specified format. For input date strings, the specified value is used to determine whether the date is specified in a valid format. Note: An input date string that uses the format *USA, *ISO, *EUR, or *JIS is always valid.



*JOB:
The format specified for the job is used. Use the Display Job (DSPJOB) command to determine the current date format for the job.


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


*EUR
The European date format (dd.mm.yyyy) is used.


*USA
The United States date format (mm/dd/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.

DATSEP
Specifies the separator used when accessing date result columns. Note: This parameter applies only when *JOB, *MDY, *DMY, *YMD, or *JUL is specified on the DATFMT parameter.



*JOB
The date separator specified for the job is used. Use the Display Job (DSPJOB) command to determine the current value for the job.


*SLASH or '/'
A slash (/) is used.


*PERIOD or '.'
A period (.) is used.


*COMMA or ','
A comma (,) is used.


*DASH or '-'
A dash (-) is used.


*BLANK or ' '
A blank ( ) is used.

DECMPT
Specifies the symbol that you want to represent the decimal point. The possible choices are:

*PERIOD
The representation for the decimal point is a period.

*COMMA
The representation for the decimal point is a comma.

*SYSVAL
The representation for the decimal point is the system value (QDECFMT).

*JOB
The representation for the decimal point is the job value (DECFMT).

DFTRDBCOL
Specifies the collection name used for the unqualified names of tables, views, indexes, and SQL packages. This parameter applies only to static SQL statements. This option will be ignored in REXX.

*NONE
The naming convention specified on the OPTION precompile parameter or by the SET OPTION NAMING option will be used.

collection-name
Specify the name of the collection. This value is used instead of the naming convention specified on the OPTION precompile parameter or by the SET OPTION NAMING option.

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. This option will be ignored in REXX.

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


DYNDFTCOL
Specifies the collection name specified for the DFTRDBCOL parameter is also used for dynamic statements. This option will be ignored in REXX.

*NO
Do not use the value specified for DFTRDBCOL for unqualified names of tables, views, indexes, and SQL packages for dynamic SQL statements. The naming convention specified on the OPTION precompile parameter or by the SET OPTION NAMING option will be used.

*YES
The collection name specified for DFTRDBCOL will be used for the unqualified names of the tables, views, indexes, and SQL packages in dynamic SQL statements.

DYNUSRPRF
Specifies the user profile to be used for dynamic SQL statements. This option will be ignored in REXX.

*USER
Local dynamic SQL statements are run under the user profile of the job. Distributed dynamic SQL statements are run under the user profile of the application server job.

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

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

*JOB or *JOBRUN
The LANGID value for the job is used.
For distributed applications, LANGID(*JOBRUN) is valid only when SRTSEQ(*JOBRUN) is also specified.



language-id
Specify a language identifier to be used. For information on the values that can be used for the language identifier, see the book International Application Development, SC41-5603-01.

NAMING
Specifies whether the SQL naming convention or the system naming convention is to be used. The possible choices are:

*SYS
The system naming convention will be used.

*SQL
The SQL naming convention will be used.

OPTLOB
Specifies whether accesses to LOBs can be optimized when accessing through DRDA. The possible choices are:

*YES
LOB accesses should be optimized. The first FETCH for a cursor determines how the cursor will be used for LOBs on all subsequent FETCHes. This option remains in effect until the cursor is closed.
If the first FETCH uses a LOB locator to access a LOB column, no subsequent FETCH for that cursor can fetch that LOB column into a LOB host variable.

If the first FETCH places the LOB column into a LOB host variable, no subsequent FETCH for that cursor can use a LOB locator for that column.


*NO
LOB accesses should not be optimized. There is no restriction on whether a column is retrieved into a LOB locator or into a LOB host variable. This option can cause performance to degrade.

RDBCNNMTH
Specifies the semantics used for CONNECT statements. This option will be ignored in REXX.

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

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.

SQLPATH
Specifies the path to be used to find procedures, functions, and user defined types in static SQL statements. This option will be ignored in REXX.

*LIBL
The path used is the library list at runtime.

character-string
A character constant with one or more collection names that are separated by blanks.

SRTSEQ
Specifies the sort sequence table to be used for string comparisons in SQL statements. Note: *HEX must be specified if a REXX procedure connects to an application server that is not a DB2 UDB for AS/400 or an AS/400 system whose release level is prior to V2R3M0.



*JOB or *JOBRUN
The SRTSEQ value for the job is used.


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


*LANGIDUNQ
The sort sequence table must contain a unique weight for each character in the code page.


*LANGIDSHR
The shared-weight sort table for the LANGID specified is used.


srtseq-table-name
Specify the name of the sort sequence table to be used with this program. The name of the sort sequence table can be qualified by one of the following library values:

*LIBL
All libraries in the user and system portions of 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.

library-name
Specify the name of the library to be searched.

TIMFMT
Specifies the format used when accessing time result columns. All output time fields are returned in the specified format. For input time strings, the specified value 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.



*HMS
The (hh:mm:ss) format is used.


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


*EUR
The European time format (hh.mm.ss) is used.


*USA
The United States time format (hh:mm xx) is used, where xx is AM or PM.


*JIS
The Japanese Industrial Standard time format (hh:mm:ss) is used.

TIMSEP
Specifies the separator used when accessing time result columns. Note: This parameter applies only when *HMS is specified on the TIMFMT parameter.



*JOB
The time separator specified for the job is used. Use the Display Job (DSPJOB) command to determine the current value for the job.


*COLON or ':'
A colon (:) is used.


*PERIOD or '.'
A period (.) is used.


*COMMA or ','
A comma (,) is used.


*BLANK or ' '
A blank ( ) is used.

USRPRF
Specifies the user profile that is used when the compiled program object is run, including the authority that the program object has for each object in static SQL statements. The profile of either the program owner or the program user is used to control which objects can be used by the program object. This option will be ignored in REXX.

*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 object is used.

*OWNER
The user profiles of both the program owner and the program user are used when the program is run.

Notes
At the start of a REXX procedure the options are set to their default value. The default value for each option is the first value listed in the syntax diagram. When an option is changed by a SET OPTION statement, the new value will stay in effect until the option is changed again or the REXX procedure ends.

For application programs, the processing options are initially set to the values specified on the CRTSQLxxx command. Each option is updated as it is encountered within a SET OPTION statement. All SET OPTION statements must precede any other embedded SQL statements.


Examples
Example 1: Set the isolation level to *ALL and the naming mode to SQL names.

EXECSQL SET OPTION COMMIT =*ALL, NAMING =*SQL


Example 2: Set the date format to European, the isolation level to *CS, and the decimal point to the comma.

EXECSQL SET OPTION DATFMT = *EUR, COMMIT = *CS, DECMPT = *COMMA

Joe
13-04-04, 13:09
Hallo.

Wenn Du in VARPG mit Embedded SQL arbeiten möchtest
sind einige Voraussetzungen notwendig.

DB2-Connect muss auf dem Client installiert und konfiguriert werden.
In den VARPG-ERstellungsoptionen muss unter DB2 der
Name der Datenbank (kann auch DB2 auf dem Client sein)
eingetragen werden.

Tip: Lass die Finger vom SQL in VARPG sondern benutze
Server-Programme auf der AS/400 die Dir die Daten liefern.

Im YAHOO gibt es ein VARPG-Forum für diese "Exoten": http://de.groups.yahoo.com/group/VARPGDE/

Gruss
Joe