SQL -199

1)SQL error -199 for RSPs

RSPs executed from the mainframe with a Use Procedure language call return the following error: cmd_fail servermsg {msg 30252 level 11 state 0 server aixsrv text {[VENDORLIB] Vendor Library Error: Incorrect syntax (DB2 PREPARE failed: DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WITH. TOKEN IS WAS EXPECTED <DB2>} } servermsg {msg 30255 level 10 state 0 server a ixsrv text {DB2 Access Module DBMS info (DB2 completed SQLCODE = -199, SQLSTATE = 42601)} } cmd_done end

2)SQL CODE = -199, ERROR: ILLEGAL USE OF KEYWORD SELECT

Description:
The "TO" command was used to externalize the Report Facility Query Text which allows the text to be written to a sequential file or a PDS.
When the Query Text is imported, which contained a replaceable parameter, the -199 was issued.
Solution:
The "TO" command does not preserve the replaceable parameter value. Issuing the "REPPARM" command when editing the query and providing a value for the replaceable parameter will resolve the -199.

3)Error: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, 
DRIVER=
ILLEGAL USE OF KEYWORD keyword.
TOKEN token-list WAS EXPECTED
Explanation: A syntax error was detected in the statement at the point where the keyword keyword appears.
As an aid to the programmer, a partial list of valid tokens is provided in SQLERRM as token-list. Only those tokens that will fit are listed. Some tokens in the list might not be valid in statements to be executed by DB2; those tokens are valid for sending to other database management systems.System action: The statement cannot be executed.
Programmer response: Examine the statement in the area of keyword keyword. A colon or SQL delimiter might be missing.
Verify that the clauses are in the correct order. If the reserved word that is identified in the messages is listed as a reserved word, make the word a delimited identifier. Refer to Appendix B of SQL Reference for a detailed list of reserved words. 
SQLSTATE: 42601
Above are list of DB2 SQL Errors and Warnings from Error -197 to -199 received while performing certain operation against DB2 Database or related products.
SQLCODE – Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by DB2 after each SQL statement is executed. DB2 conforms to the ISO/ANSI SQL standard as follows:
If SQLCODE = 0, execution was successful.
If SQLCODE > 0, execution was successful with a warning.
If SQLCODE < 0, execution was not successful.
SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.
SQLSTATE – SQLSTATE is also set by DB2 after the execution of each SQL statement. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.
Hope this was helpful.
Thanks,
SQLServerF1 Team
Information about DB2 SQL Error Codes and Error Messages on Windows, Linux and Z/OS Operating Systems.


4)-199 ILLEGAL USE OF KEYWORD keyword. TOKEN token-list WAS EXPECTED
Explanation
A syntax error was detected in the statement at the point where the keyword keyword appears.
As an aid to the programmer, a partial list of valid tokens is provided in SQLERRM as token-list. Only those tokens that will fit are listed. Some tokens in the list might not be valid in statements to be executed by DB2®; those tokens are valid for sending to other database management systems.

 5)About: SQL Error -199 During APS MVS Install For DB2 (With DB2 4.1)



 Problem
An SQL -199 error (illegal use of keyword char()) may be encountered during the DB2 portion of the APS MVS installation. This happens with MVS DB2 4.1 or later. This error results because APS uses CHECK as a column name and CHECK is now a reserved keyword in DB2 4.1  

Resolution
The problem can be bypassed by editing the affected APS JCL, locating CHECK and surrounding it in double quotes.  

6) Error: DB2 SQL Error: SQLCODE=-197, SQLSTATE=42877, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=,

DRIVER=
A QUALIFIED COLUMN NAME IS
NOT ALLOWED IN THE ORDER BY
CLAUSE WHEN A SET OPERATOR IS
ALSO SPECIFIED
Explanation: The ORDER BY clause of a fullselect that
includes a set operator, such as UNION, EXCEPT, or
INTERSECT, cannot have qualified column names.
System action: The statement cannot be processed.
Programmer response: Change the statement to
ensure that all column names in the ORDER BY clause
are unqualified.
SQLSTATE: 42877



7) Error: DB2 SQL Error: SQLCODE=-198, SQLSTATE=42617, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY
Explanation: The operand (host variable or string constant) that was the object of the PREPARE or EXECUTE IMMEDIATE statement either contained all blanks or was an empty string. A DBRM built in
Version 2 Release 3 cannot be used on a Version 2
Release 2 system if the distributive functions were used. If this error appears on Version 2 Release 2 and the DBRM was built on Version 2 Release 3, then the program needs to be precompiled again to correct the
problem.
System action: The statement cannot be executed.
Programmer response: Correct the logic of the application program to ensure that a valid SQL
statement is provided in the operand of the PREPARE or EXECUTE IMMEDIATE statement before that statement is executed.
SQLSTATE: 42617

8)ILLEGAL USE OF KEYWORD keyword. TOKEN token-list WAS EXPECTED

Explanation

A syntax error was detected in the statement at the point where the keyword keyword appears.

As an aid to the programmer, a partial list of valid tokens is provided in SQLERRM as token-list. Only those tokens that will fit are listed. Some tokens in the list might not be valid in statements to be executed by DB2®; those tokens are valid for sending to other database management systems.

System action

The statement cannot be processed.

Programmer response

Examine the statement in the area of keyword keyword. A colon or SQL delimiter might be missing.

Verify that the clauses are in the correct order. If the reserved word that is identified in the messages is listed as a reserved word, make the word a delimited identifier.

9)SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD AS. TOKEN INCLUDE PARTITIONED PARTITION CLUSTER

Error description

The SQL error with code=-199 is being caused by trying to create a DB2 index with multiple key targets, one of which contains XML pattern (the DERIVED_FROM column is not empty). This error may occur starting with DB2 Version 9 new-function mode, because XML columns are not supported prior to DB2 Version 9.

 

10)DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WITH,ED TOKEN ( SET

Error description

1) DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WITH, TOKEN ( SET DELETE INSERT SELECT UPDATE VALUES DSNHATTR WAS EXPECTED on SQL PA V2R2 R220. ADDITIONAL SYMPTOMS: 

1) Default statistics will now be employed for any "materialized" table or work file, including those created by an outer join process, a common table expression, etc. MQTs in V8 NFM, which do have real catalog statistics, are not included in this group. Only tables created 'on-the-fly' will acquire the default stats. 

2) Parsing a DECLARE CURSOR WITH HOLD | RETURN FOR...clause was being misinterpreted as a common table expression, such as those beginning "WITH (SELECT ...) AS T1". 

3) Order By and Group By clauses, especially those appearing following a Join process, were not always recorded in reports or processed for overhead. 

4) Under TSO, if no valid SQL was processed, the QLIMIT report is not populated. When selecting this as option 4 from the Reports Menu, the browsing of this empty data set caused ISPF error recovery, and kicked the user out of SQL PA and all the way back to the ISPF main menu. The ANLLIMIT clist has been modified to test for an 'empty' QLIMIT report file, and present a message to the screen before returning to the Reports menu, avoiding the ISPF Browse.

 5) Users of the PLI stored procedure sample programs, ANLSTP2C and ANLSTP2R, provide two parms to the EXEC card in the JCL that pass the DB2 subsystem and default high level qualifier. These were fixed field parms and had to be exactly 4 bytes and 8 bytes respectively, with a comma between. Now, the parameters can be variable length and there is no fixed field restriction: PARM='DSN,MYAUTH' is perfectly acceptable, whereas PARM='DSN ,MYAUTH ' was required beforehand. This is to satisfy MR0330053312. 

6) MSGANL3034E on SQL PA run against some DBRMs. ANL3034E (PLI Oncode errors). ANL1070W can also be issued. 

7) Resolves the underlying problem of extracting a proper Host variables count when SQL is contained in a short (<=56 byte) record. 

8) Increase maximum number of tables processed in a single ANLCAT22 (statistics migration) run from 200 to 500 tables, table spaces or indexes.

 9) Under TSO interface, identify when a sequential data set is presented for Edit/Input and a member name was incorrectly specified, and also identify when a Member name is not found in the PDS as requested. 

10)Create new parameter SUBVERS for the Target host's library (ANL220.SANLDATA) to allow the choice of the proper load library/program for execution based on the current release level for that subsystem (SVERS), rather than by SANLDATA member name (QHOST). MR0420056354 MR0223054949 

11)With the introduction of an interface to the DB2 Easy Explain FUnction (EEE) the following enhancements are available: - provide the ability to retro-explain and cost out an existing plan from a PLAN_TABLE, either through EEE or directly within SQL PA, using the new EXPLAIN OLD parm. Satisfies MR1108013230 MR0227046633 MR0816046440 - provide a shorthand, abbreviated Explain report. This can be provided by EEE standalone, as well as by SQLPA in the sysout log when using the DBTRACE ALL|DMP parm in ANLCNTL. - Provide the ability to read SQL from the catalog Plans and Packages, via EEE integrated tool. Satifies MR0715033149 - Provide a comparison of the cost differences between an OLD plan and NEW plan, and show differences in total costs as well as DSN_STATEMNT estimated costs. Satifies MR1126033641 - Provide and explain input from programming source code and make statements explainable by removing special characters, etc., accomplished by importing SQL through EEE interface. Satisfies MR0816045414 

12)Upgrade the TSO logo program release levels to V2.2.3 level and integrate the EEE clist, panel, and JCL modifications. Add Appendix D, Interfacing with EEE, to SQLPA documentation. 

13) MSGANL1021E processing SQL statements greater than 32K. SQL > 32K ANL1021E.