UV Stage-SQLSTATE=S1000, DBMS.CODE=950088

ushas · Post by **ushas** » Sun Mar 30, 2008 11:10 am

Can any one help to resolve this error?
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT". Scanned command was SELECT |

This job worked fine previously and i am trying to run after re-indexing Datastage!![size=12][/size]

ray.wurlod · Post by **ray.wurlod** » Sun Mar 30, 2008 3:17 pm

Welcome aboard.

If the query parser is complaining about SELECT then there is something wrong with the table name in your UV stage. Can you please post the entire error message?

ushas · Post by **ushas** » Mon Mar 31, 2008 2:58 am

DSD.BCIOpenRef call to SQLPrepare failed.
Statement was:SELECT column1,column2,column3,column4 FROM "abc"
WHERE
column1 = :1
AND
((column2 = 0 AND column3 =0)
OR
( column3 <= :2 AND column2 > :3))
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT". Scanned command was SELECT |

This query is in reference link and the table name using is "bcd" for an example.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 31, 2008 3:18 am

I need you to post the dictionary listing of the UV table. To get this, execute the command

Code: Select all

LIST DICT tablename

Either the table name itself, or one or more of the column names (or both), just isn't working.

ushas · Post by **ushas** » Mon Mar 31, 2008 4:27 am

This is my TableDefinition

<a href="http://s290.photobucket.com/albums/ll26 ... leDict.jpg" target="_blank"><img src="http://i290.photobucket.com/albums/ll26 ... leDict.jpg" border="0" alt="Photobucket"></a>

ray.wurlod · Post by **ray.wurlod** » Mon Mar 31, 2008 3:14 pm

There's no columns there called "column1", "column2", etc. Like with any other database you must use the column names defined for the table. Also, these must coincide in number, order and data type with the columns defined in your job design.

Code: Select all

SELECT DEBT_INSTRUMENT_REPAYMENT_SCHED, MINIMUM_MATURITY, MAXIMUM_MATURITY, RESIDENTIAL_MATURITY, RESIDENTIAL_MATURITY_CODE_SEQ FROM "ResidentialMaturityCode";

If you import the table definition into DataStage, then the UV stage into which you load that imported table definition will generate correct SQL.

ushas · Post by **ushas** » Tue Apr 01, 2008 2:33 am

Sorry for that!!

The column names are column1,column2,column3,column4 resp/-

First 3 columns are primary key field

The design is we are generating the hash file in 1 job and in another job using UniVerse stage we are invoking the same hash file.

I am able to read the data but not able to run the job!!!
Here it goes with the error message and table structure.
/* Error
BCIOpenRef call to SQLPrepare failed.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 01, 2008 2:45 am

Are the columns in precisely the same order in all jobs and all stages? Navigation in hashed files is by position, not by column name. This includes the order within the key column.

Note: correct terminology is "hashed" file, not "hash" file.

What columns have you defined in the job that is executing this SQL? There must be three columns marked as Key - this is NOT the primary key but, rather, the search key. These three columns (in their order) will provide the three values with which to replace the three parameter markers in the WHERE clause. These values will be provided from the Key Expression field in the Transformer stage.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 01, 2008 3:14 am

The table whose file dictionary you listed was "ResidentialMaturityCode" yet the name you give in the SQL query is "pilresidmatlovmap". I daresay that the query parser is complaining about a non-existent table name. This is borne out by the fact that it gave up parsing immediately after the SELECT token - it had not found a valid "FROM" clause from which to select.

ushas · Post by **ushas** » Tue Apr 01, 2008 3:16 am

But my hash file which i created is pilresidmatlovmap

ray.wurlod · Post by **ray.wurlod** » Tue Apr 01, 2008 3:44 am

So what is the file dictionary for THAT ?

ushas · Post by **ushas** » Tue Apr 01, 2008 3:56 am

Previously my design, created the hash file via UniVerse stage and also accessed the hash file via Universe stage.For this we needed some permisions to run the job.
So,next we tried to change the design as the one specified as above which is running in some other environment.

May be i am not clear about using the UniVerse stage.Thats why not able to debug!!
But my hash file which i created is pilresidmatlovmap.

I also ran with the option by giving the hash file name as the table name and ran one more time by removing the table name which no longer exists now as because the table name was created previously with the older design as part of hash file creation.

Still the same error persists!!!

ray.wurlod · Post by **ray.wurlod** » Tue Apr 01, 2008 4:26 am

You are very unclear about what has happened, what is happening and what will happen. Please answer the following questions.

1. Is the hashed file still created? If so, what is the name of that hashed file?

2. Is there a VOC pointer to it in the project (either because the hashed file was created in the account or because a SETFILE command has been executed)? If so, what is the name of that VOC pointer?

3. Please provide a file dictionary listing of the hashed file pilresidmatlovmap.

ushas · Post by **ushas** » Tue Apr 01, 2008 4:35 am

Yes ..Its been created in the name 'pilresidmatlovmap'

ray.wurlod · Post by **ray.wurlod** » Tue Apr 01, 2008 4:36 am

That leaves questions 2 and 3 to be answered.