473,545 Members | 1,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

snapshot tbsp table functions via jdbc

Hello all,

I'm trying to call the snapshot table function SNAP_GET_TBSP via a jdbc
connection using the following query:

SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1), -1)) AS T

In CLP this works properly, but when I execute it from java, I get the
following SQL exception:

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

If I call a table function that doesn't take a varchar param (like
SNAPSHOT_DBM), the exception doesn't occur. It seems to me that the
varchar parameter of the snapshot function is responsible for the
problem. I already tried to use a prepared statement and provide the
parameter with different encodings, but then I get an exception that a
prepared statement can't be used with this kind of query...

The database version is 8.2; I'm using the IBM db2 universal driver.

Any help or hint is welcome

Hans

Jul 4 '06 #1
8 4542
SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)-!--)--!--,
-1)) AS T
veffen 写道:
Hello all,

I'm trying to call the snapshot table function SNAP_GET_TBSP via a jdbc
connection using the following query:

SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1), -1)) AS T

In CLP this works properly, but when I execute it from java, I get the
following SQL exception:

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

If I call a table function that doesn't take a varchar param (like
SNAPSHOT_DBM), the exception doesn't occur. It seems to me that the
varchar parameter of the snapshot function is responsible for the
problem. I already tried to use a prepared statement and provide the
parameter with different encodings, but then I get an exception that a
prepared statement can't be used with this kind of query...

The database version is 8.2; I'm using the IBM db2 universal driver.

Any help or hint is welcome

Hans
Jul 4 '06 #2
Hello,

ok, thanks for the reply, and sorry, somehow I forgot that closing
brace here, but it was in the original statement. (Otherwise I'd get a
syntax error...)

Here's the statement COPIED from my source code

SELECT T.TBSP_ID FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)),-1))
AS T

No missing braces this time, but the exception is thrown anyway...

And here's the exception, also copied...

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

Any help is appreciated

Hans
Hardy schrieb:
SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)-!--)--!--,
-1)) AS T
veffen 写道:
Hello all,

I'm trying to call the snapshot table function SNAP_GET_TBSP via a jdbc
connection using the following query:

SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1), -1)) AS T

In CLP this works properly, but when I execute it from java, I get the
following SQL exception:

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

If I call a table function that doesn't take a varchar param (like
SNAPSHOT_DBM), the exception doesn't occur. It seems to me that the
varchar parameter of the snapshot function is responsible for the
problem. I already tried to use a prepared statement and provide the
parameter with different encodings, but then I get an exception that a
prepared statement can't be used with this kind of query...

The database version is 8.2; I'm using the IBM db2 universal driver.

Any help or hint is welcome

Hans
Jul 5 '06 #3
veffen wrote:
Hello,

ok, thanks for the reply, and sorry, somehow I forgot that closing
brace here, but it was in the original statement. (Otherwise I'd get a
syntax error...)

Here's the statement COPIED from my source code

SELECT T.TBSP_ID FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)),-1))
AS T

No missing braces this time, but the exception is thrown anyway...

And here's the exception, also copied...

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

Any help is appreciated

Hans
Hardy schrieb:
>SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)-!--)--!--,
-1)) AS T
veffen 写道:
Hello all,

I'm trying to call the snapshot table function SNAP_GET_TBSP via a jdbc
connection using the following query:

SELECT * FROM TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1), -1)) AS T

In CLP this works properly, but when I execute it from java, I get the
following SQL exception:

DB2 SQL error: SQLCODE: -873, SQLSTATE: 53090, SQLERRMC: Different
encoding schemes in one statement

If I call a table function that doesn't take a varchar param (like
SNAPSHOT_DBM), the exception doesn't occur. It seems to me that the
varchar parameter of the snapshot function is responsible for the
problem. I already tried to use a prepared statement and provide the
parameter with different encodings, but then I get an exception that a
prepared statement can't be used with this kind of query...

The database version is 8.2; I'm using the IBM db2 universal driver.

Any help or hint is welcome

Hans
Can you post some more code, because it works for me.

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jul 5 '06 #4
Are you playing with unicode tables in a non unicode database?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 5 '06 #5
Thanks for your answers.

I did the following to examine your point:

Class.forName(" com.ibm.db2.jcc .DB2Driver");
Properties props = new Properties();
props.put("user ","****");
props.put("pass word","****");
// props.put("char Set", "ISO-8859-1");
Connection conn =
DriverManager.g etConnection("j dbc:db2://****:****/****",props);
conn.setAutoCom mit(false);
Statement st = conn.createStat ement();

printResultSet( st.executeQuery ("SELECT
DBPARTITIONNUM, REG_VAR_NAME,RE G_VAR_VALUE FROM
TABLE(REG_LIST_ VARIABLES()) AS T WHERE REG_VAR_NAME = 'DB2CODEPAGE'") );
printResultSet( st.executeQuery ("SELECT TYPESCHEMA,TYPE NAME,CODEPAGE
FROM SYSCAT.DATATYPE S"));
printResultSet( st.executeQuery ("SELECT
TABSCHEMA,TABNA ME,COLNAME,TYPE SCHEMA,TYPENAME ,CODEPAGE FROM
SYSCAT.COLUMNS WHERE (CODEPAGE <0) AND (CODEPAGE <819)"));

printResultSet( st.executeQuery ("SELECT * FROM
TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)),-1)) AS T"));

The first two statements gave me the following results:

DBPARTITIONNUM REG_VAR_NAME REG_VAR_VALUE
0 DB2CODEPAGE 819

TYPESCHEMA TYPENAME CODEPAGE
SYSIBM BIGINT 0
SYSIBM BLOB 0
SYSIBM BOOLEAN 0
SYSIBM CHARACTER 819
SYSIBM CLOB 819
SYSIBM DATALINK 819
SYSIBM DATE 0
SYSIBM DECIMAL 0
SYSIBM DOUBLE 0
SYSIBM INTEGER 0
SYSIBM LONG VARCHAR 819
SYSIBM REAL 0
SYSIBM REFERENCE 0
SYSIBM SMALLINT 0
SYSIBM TIME 0
SYSIBM TIMESTAMP 0
SYSIBM VARCHAR 819

The third statement issued a list of varchar columns with CCSID 1208
(besides a never-ending list of columns with 819, which I had to
suppress). Do you think (or know) that those columns are responsible
for the exception, because the snap_tbsp function sweeps all columns
and can't cope with those two encoding types in the same statement? But
why does the very same statement work in the CLP, then?
It's a SAP database (hence I don't "play around"...), and everything
works fine, except for my little query in java :-(

Regards,

Hans

Serge Rielau schrieb:
Are you playing with unicode tables in a non unicode database?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 7 '06 #6
veffen wrote:
Thanks for your answers.

I did the following to examine your point:

Class.forName(" com.ibm.db2.jcc .DB2Driver");
Properties props = new Properties();
props.put("user ","****");
props.put("pass word","****");
// props.put("char Set", "ISO-8859-1");
Connection conn =
DriverManager.g etConnection("j dbc:db2://****:****/****",props);
conn.setAutoCom mit(false);
Statement st = conn.createStat ement();

printResultSet( st.executeQuery ("SELECT
DBPARTITIONNUM, REG_VAR_NAME,RE G_VAR_VALUE FROM
TABLE(REG_LIST_ VARIABLES()) AS T WHERE REG_VAR_NAME = 'DB2CODEPAGE'") );
printResultSet( st.executeQuery ("SELECT TYPESCHEMA,TYPE NAME,CODEPAGE
FROM SYSCAT.DATATYPE S"));
printResultSet( st.executeQuery ("SELECT
TABSCHEMA,TABNA ME,COLNAME,TYPE SCHEMA,TYPENAME ,CODEPAGE FROM
SYSCAT.COLUMNS WHERE (CODEPAGE <0) AND (CODEPAGE <819)"));

printResultSet( st.executeQuery ("SELECT * FROM
TABLE(SNAP_GET_ TBSP(CAST(NULL AS VARCHAR(1)),-1)) AS T"));

The first two statements gave me the following results:

DBPARTITIONNUM REG_VAR_NAME REG_VAR_VALUE
0 DB2CODEPAGE 819

TYPESCHEMA TYPENAME CODEPAGE
SYSIBM BIGINT 0
SYSIBM BLOB 0
SYSIBM BOOLEAN 0
SYSIBM CHARACTER 819
SYSIBM CLOB 819
SYSIBM DATALINK 819
SYSIBM DATE 0
SYSIBM DECIMAL 0
SYSIBM DOUBLE 0
SYSIBM INTEGER 0
SYSIBM LONG VARCHAR 819
SYSIBM REAL 0
SYSIBM REFERENCE 0
SYSIBM SMALLINT 0
SYSIBM TIME 0
SYSIBM TIMESTAMP 0
SYSIBM VARCHAR 819

The third statement issued a list of varchar columns with CCSID 1208
(besides a never-ending list of columns with 819, which I had to
suppress). Do you think (or know) that those columns are responsible
for the exception, because the snap_tbsp function sweeps all columns
and can't cope with those two encoding types in the same statement? But
why does the very same statement work in the CLP, then?
It's a SAP database (hence I don't "play around"...), and everything
works fine, except for my little query in java :-(
Yep.. One of these special SAP thingies.
try:
SELECT T.* FROM SYSIBM.SYSDUMMY 1, TABLE(SNAP_GET_ TBSP(CAST(NULL AS
VARCHAR(1)),-1)) AS T
or
SELECT T.* FROM SYSIBM.SYSDUMMY 1, TABLE(SNAP_GET_ TBSP(CAST(NULL AS
VARCHAR(1) FOR SBCS DATA ),-1)) AS T

What's happening here is that DB2 needs to decide whether a statement
should be Unicode or DB codepage. The first thing the parser runs into
is likely the CAST. It decides (for SAP) to go with Unicode and then you
get swear words when it discovers that SNAP_GET_TBSP isn't Unicode.
The first fix pre-empts the CAST with SYSIBM.SYSDUMMY 1 (a view in DB
code page), so DB2 commits to db-codepage.
The second fix (tries) the same trick by influencing the CAST itself.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 7 '06 #7
PS: The reason why it works from CLP is that CLP is not a unicode
client. All this fancyness kicks in for Unicode clients to allow Unicode
literals and such to talk to unicode tables unpestered.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 7 '06 #8
It works! Thanks so much! And thanks for the explanations.

Best regards,

Hans

Serge Rielau schrieb:
PS: The reason why it works from CLP is that CLP is not a unicode
client. All this fancyness kicks in for Unicode clients to allow Unicode
literals and such to talk to unicode tables unpestered.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 7 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2257
by: Nils Valentin | last post by:
Hi MySQL Fans ;-), Is it possible that the 3.08 series allows to connect to 4.0.14 versions but not to the 4.1 alpha-versions ? I get belows error when tryig to connect from DbVisualizer which was working fine until a while ago. I haven't watched it recently, and haven't changed anything I am aware off right now. After I realized the...
3
1627
by: John Gibson | last post by:
Hi, all. I have a table which is continually updated with the latest totals. I would like to take snapshots of some of the data in that table and store it in a second table to run statistics on it later. What might some ways of doing this be? Illustrative (I hope) example using fruit-qty-on-hand at a grocery store: Fruit_table ...
8
2980
by: Jean-Marc Blaise | last post by:
Dear all, It seems there is a problem with some snapshot table functions, if you try to divide by some element that is unset or equal to 0. This generates a trap file with the db2fmp.exe process, that keeps going on, until your disk is full. I'm on W2K, DB2 UDB 8.1 FP4. Test case: db2start
1
2283
by: Prince Kumar | last post by:
Is there anyway to display the partition number while taking snapshot on all the partitions (-2) using the snapshot function? ex, select TABLESPACE_NAME, TOTAL_PAGES from table(SNAPSHOT_TBS_CFG ( ' ', -2 )) as snap. Is there anyway, I can get the partition number for which the data is being retrieved?.
4
3206
by: Civilian_Target | last post by:
Is there any way in DB2 to automate the taking of a snapshot, for example, a function that will cause a db2 snapshot to be taken every 5 minutes and written to a uniquely named file? Am I better off attempting to do this with my poor knowledge of dos batch files? I'm running UDB 8.2 on Windows 2000. Thanks.
14
14789
by: Ina Schmitz | last post by:
Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see the logical and physical reads from bufferpool for every statement monitored. Am I right? But now, I would like to read these results out of a table...
10
26070
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))
1
2074
by: PaulR | last post by:
Hi all, (DB2 LUW 8.2) I'm just trying to write a little widget to capture lock wait details (when they occur). i.e when a lock-wait occurs, simply record the 2 statements involved in the lock wait. However, I am stumped by the fact that the snapshot monitor does not
4
4418
by: MPD | last post by:
Hi How can I create a job in sql agent to create a new snapshot every hour? I have, for eg a T-SQL that does it manually. create database Snapshotter_snap_20070418_1821 on ( name = Snapshotter, filename = 'c:\temp\Snapshotter_snap_20070418_1821.ss') as snapshot of Snapshotter
0
7459
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main...
0
7393
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7803
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7411
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5322
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3444
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3439
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1012
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
695
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.