CLI0165E Error in row - while lookup

[suneer333]

Hi,

I get this at a lookup_ext in a Data Flow. The database is DB2. Below pasted is the error log. Wonder if this is something to be done with the DBAs.

*************************************************
ODBC data source <PRODEDW> error message for operation <SQLFetchScroll>: <[IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01>.
Data flow DF_Job|Transform q_MeasuresAndKeys-Function41
Function call <lookup_ext ( 201207, 00000061, 1981.04.10 00:00:00, -1, POSITION_KEY ) > failed, due to error <70401>: <ODBC
data source <PRODEDW> error message for operation <SQLFetchScroll>: <[IBM][CLI Driver] CLI0165E Error in row.
SQLSTATE=01S01>.>.
Data flow DF_Job|Transform q_MeasuresAndKeys-Function41
The above error occurs in the context <|Data flow DF_Job|Transform q_MeasuresAndKeys-Function41|lookup_ext(...) Function Body|>.
Session HR_Datamart_Load Caught an Error
(12.2) 07-25-12 22:23:27 (E) (13136:4130286096) RUN-050304: |Session HR_Datamart_Load
Function call <raise_exception_ext ( Caught an Error, 1 ) > failed, due to error <50316>: <Caught an Error>.
(12.2) 07-25-12 22:23:27 (E) (13136:4130286096) RUN-050316: |Session HR_Datamart_Load Caught an Error

**********************************************

Any ideas highly appreciated.

[Werner Daehn]

I have no idea. I can't even find much info about that CLI0165E error!?!

Run the job with the trace option "trace sql functions=yes" for a try? Maybe we get lucky when looking at the actual statement sent.

[Tarunvig]

Hi..

what Logic are you using in the Lookup.. And what is the return column????
Is it date?

can you try changing the date column to a varchar?

can you paste a snap shot of the lkp_extn ?? I think its a problem with lookup or return value..

[suneer333]

It is returning POSITION_KEY.

There are 24 lookup_ext in one transform and another 33 lookups in a sequential one, all using PRE-LOAD CACHE.

I threw the same look up query which is shown in the error log, into the database thinking that this might be a data related issue, it works well.

I tried with the DBA, he could not figure anything out.

I toggled the PRE-LOAD CACHE of the lookup which is displayed in error log (retrieving POSITION_KEY) into NO-CACHE (this was to enable the SQL Function trace), and the error went off.

Server guy says that there is enough disk memory free (32 GB). (This DF uses a pageable cache). Still, he cleaned up and freed almost 60 GB of disk space.

I toggled this back to PRE-LOAD cache to recreate the error, but it is fine now. I am not clueless on what change needs to be done at PROD level.

[suneer333]

Update:

Setting to NO-CACHE seems to be very slow.

Suspecting the network to be a bottleneck , when we find that there is no load on the DB and and Server overload.

Eventhough the db initiates 40 connections when the load starts, all the processes seem to be waiting in the DB side.

[suneer333]

Has nobody come across this? Any thoughts?

Hi Werner,

I enabled the trace - SQL Function and SQL Reader -
The trace message does not give any information.

Maybe 57 lookups in a single DF might have issues ?? But this has been running fine till recently.

When I change the cache settings of this lookup, it crosses the particular point. Cannot confirm that this is a data issue.

@Tarun: I am not returning a date column, rather it is an int.

The lookup_ext is:

lookup in table RM_DS.RM_USER.POSITION (PRE_LOAD_CACHE)
Find all records with where clause
SNAPSHOT_KEY=$G_SNAPSHOT_KEY AND
POSITION_NBR=q_Lookups.POSITION_NBR AND
EFFDT<=q_Lookups.EFFDT
If multiple rows are found, take the MAX row based on column EFFDT

[Werner Daehn]

For no_cache, does you lookup table have one index with the columns
SNAPSHOT_KEY, POSITION_NBR, EFFDT


No 57 lookups is not that much. It is 57 connections.
Did you use the lookup as mapping or as new-function-call? The latter I assume which would be fine.

[suneer333]

I am using the lookup as a function call. The only index on the table is POSITION_KEY.

When I use PRE-LOAD Cache for all lookups, the load fails at exact same point. Giving the exact message as the culprit. If I change the lookup cache to 'NO', I can see that the read through the transform goes further, but I dont think that means that it must have past this record as we do not know the order of read then.

Now here are a few questions

1) Should I change the datastore Locale? All code page / server code pages are set to default now.
2) Is there a patch or something to be installed in database client in server side?
3) Should I try any conversion function in the lookup_ext for the supplied and retrieved values?

DS version is - 12.2.0.0

[suneer333]

I filtered out to 28 records with POSITION_NBR = '00000061'

It still gives me the error.

Function call <lookup_ext ( 201207, 00000061, 1981.04.10 00:00:00, -1, POSITION_KEY ) > failed, due to error <70401>: <ODBC data source <PRODEDW> error message for operation <SQLFetchScroll>: <[IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01>.>.

Error 70401 says so:
"ODBC data source <%s> error message for operation <%s>: <%s>."

Why is this happening to these records?

[manoj_d]

check the following threads which may help understanding what causes this error

http://www-01.ibm.com/support/docview.wss?uid=swg21334737
http://www.ibm.com/developerworks/forums/thread.jspa?threadID=237160

[Arun.K]

A thought, Probably an issue with the version or fixes in middle ware?? Also do you have a data type issue some where in the lookups?

Arun