CurrentSchema in db2cli.ini file

[yadutektips]

Hi,

I'm trying to query a table from my message broker. As the schemas are different,

Earlier, without the schema name, I was getting exceptions by my message flow.

To avoid this, I had to hard code the schema name in my code.

In order to avoid the hard coding, I added the CurrentSchema value in the Db2cli.ini file. Now, i'm not getting the exceptions but the query is returning nothing.

Both the database and the messge broker are on windows.
Below are the contents of the db2cli.ini file.

[SAMPLE]
CURRENTSCHEMA=SQLJ
DBALIAS=SAMPLE
UID=sampay




Help please!!!

TIA
Yadu

 

[rbod]

Did you make sure that the query returns results without the changes to 'ini' file?
Your ini file looks ok to me.

 

[yadutektips]

when i remove the schema name from the ini file, i get an exception in my code as in windows odbc, the schema will be defaulted to the user id.

 

[juliane26]

Try setting it in the CLI settings from a GUI, I remember a fixpak level where the db2cli.ini wasn't read properly but it had to be set there.
Using the GUI you can check what DB2 is making out of it.

In the configuration Assistant, go to Settings -> selected -> CLI settings and try setting it here.

Another option would be to use CURRENT SQLID.



Juliane

 

[yadutektips]

Juliane,

I've tried setting it up from the GUI as well. I tried with both SQLID and CLISCHEMA as well but the response remains the same.

I tried running a trace on ODBC. Nothing was generated from the trace.

I had an event monitor running on the data and I'm pasting the contents below. This might give an indication.

The table has just one row which satisfies the query condition. Also please find the query below.

SELECT R.* FROM Database.EM_SUBSCRIPTION AS R
WHERE
R.EM_SUBSCRIPTION_NUMBER = 99


The Statement Event Contents of Event Monitor


Type : Dynamic
Operation: Open
Section : 4
Creator : NULLID
Package : SYSSH200
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSH200C4
Cursor was blocking: TRUE
Text : SELECT * FROM EM_SUBSCRIPTION R WHERE (R.EM_SUBSCRIPTION_NUMBER)=(99)
-------------------------------------------
Start Time: 10/17/2005 11:23:27.265252
Stop Time: 10/17/2005 11:23:27.265369
Exec Time: 0.000117 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

28849) Statement Event ...
Appl Handle: 146
Appl Id: *LOCAL.DB2.051017102049
Appl Seq number: 0002

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 4
Creator : NULLID
Package : SYSSH200
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSH200C4
Cursor was blocking: TRUE
Text : SELECT * FROM EM_SUBSCRIPTION R WHERE (R.EM_SUBSCRIPTION_NUMBER)=(99)
-------------------------------------------
Start Time: 10/17/2005 11:23:27.265252
Stop Time: 10/17/2005 11:23:27.267051
Exec Time: 0.001799 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 1
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 1
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 1
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 100
sqlstate: 02000

 

[juliane26]

Hi,

I am sorry, but sql code 100 with SQL state 02000:

db2 => ? sql0100
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.

Explanation:
One of the following conditions is true:
o No row was found that meets the search conditions specified in an UPDATE or DELETE statement.
o The result of a SELECT statement was an empty table.
o A FETCH statement was executed when the cursor was positioned
after the last row of the result table.
o The result of the SELECT used in an INSERT statement is
empty.

No data was retrieved, updated, or deleted.
User Response:
No action is required. Processing can continue.
sqlcode : +100
sqlstate : 02000

That means, it can find the table, but the resultset is empty.
You have more than one table EM_SUBSCRIPTION with different schemas?

Juliane

 

[yadutektips]

THere is no other table with the same name in other schemas. What I cannont understand from the event monitor output is that the query is executed twice.

In the first instance, the SQLCODe and sqlstate both are zeros. As there is only one row in the table, I presume, the SQLCODE 100 is generated when the query is executed for the second time.

 

[juliane26]

well, for second, this is an SELECT statement and not a DELETE, so the resultset should be the same if not somebody else deletes. A select doesn't change your data.

I never worked with the event monitor, how often is actually ececuted can be seen as a number in the SQL snapshot (db2 get snapshot for dynamic sql ...). Dunno how this has to be correctly interpreted here.

Suggestion: check your table data whether is is actually there. And which error code gets your application itself ? You catch any exception ?

From what I see I still think the resultset is empty, it is not complaining with SQL0204: undefined name. Something like that is what I would expect when the schema is wrong.




Juliane