BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

CLI0165E Error in row - while lookup


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Thu Jul 26, 2012 1:21 am 
Post subject: CLI0165E Error in row - while lookup

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.

_________________
Regards,
Suneer
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Thu Jul 26, 2012 11:51 am 
Post subject: Re: CLI0165E Error in row - while lookup

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.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
Tarunvig
Principal Member
Principal Member



Joined: 18 Jan 2011

Posts: 222
Location: Alexandria,VA


flag
PostPosted: Thu Jul 26, 2012 12:19 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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..
Back to top
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Thu Jul 26, 2012 5:34 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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.

_________________
Regards,
Suneer
Back to top
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Thu Jul 26, 2012 6:13 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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.

_________________
Regards,
Suneer
Back to top
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Fri Jul 27, 2012 1:18 am 
Post subject: Re: CLI0165E Error in row - while lookup

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

_________________
Regards,
Suneer
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Fri Jul 27, 2012 3:50 am 
Post subject: Re: CLI0165E Error in row - while lookup

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.

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Fri Jul 27, 2012 12:34 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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

_________________
Regards,
Suneer
Back to top
suneer333
Principal Member
Principal Member



Joined: 15 Jul 2010
ASUG Icon
Posts: 287
Location: Washington


flag
PostPosted: Fri Jul 27, 2012 10:03 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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?

_________________
Regards,
Suneer
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2944



PostPosted: Sun Jul 29, 2012 5:01 pm 
Post subject: Re: CLI0165E Error in row - while lookup

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
Back to top
Arun.K
Forum Associate
Forum Associate



Joined: 18 Oct 2011

Posts: 852
Location: Glen Allen, Virginia


flag
PostPosted: Mon Jul 30, 2012 11:11 am 
Post subject: Re: CLI0165E Error in row - while lookup

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
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0504 seconds using 17 queries. (SQL 0.0221 Parse 0.0009 Other 0.0273)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo