snapshot tbsp table functions via jdbc

[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

[Hardy]

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

[veffen]

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

[Gregor Kova�]

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**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

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/

[veffen]

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("password","****");
// props.put("charSet", "ISO-8859-1");
Connection conn =
DriverManager.getConnection("jdbc:db2://****:****/****",props);
conn.setAutoCommit(false);
Statement st = conn.createStatement();

printResultSet(st.executeQuery("SELECT
DBPARTITIONNUM,REG_VAR_NAME,REG_VAR_VALUE FROM
TABLE(REG_LIST_VARIABLES()) AS T WHERE REG_VAR_NAME = 'DB2CODEPAGE'"));
printResultSet(st.executeQuery("SELECT TYPESCHEMA,TYPENAME,CODEPAGE
FROM SYSCAT.DATATYPES"));
printResultSet(st.executeQuery("SELECT
TABSCHEMA,TABNAME,COLNAME,TYPESCHEMA,TYPENAME,CODE PAGE 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/

[Serge Rielau]

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("password","****");
// props.put("charSet", "ISO-8859-1");
Connection conn =
DriverManager.getConnection("jdbc:db2://****:****/****",props);
conn.setAutoCommit(false);
Statement st = conn.createStatement();

printResultSet(st.executeQuery("SELECT
DBPARTITIONNUM,REG_VAR_NAME,REG_VAR_VALUE FROM
TABLE(REG_LIST_VARIABLES()) AS T WHERE REG_VAR_NAME = 'DB2CODEPAGE'"));
printResultSet(st.executeQuery("SELECT TYPESCHEMA,TYPENAME,CODEPAGE
FROM SYSCAT.DATATYPES"));
printResultSet(st.executeQuery("SELECT
TABSCHEMA,TABNAME,COLNAME,TYPESCHEMA,TYPENAME,CODE PAGE 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.SYSDUMMY1, TABLE(SNAP_GET_TBSP(CAST(NULL AS
VARCHAR(1)),-1)) AS T
or
SELECT T.* FROM SYSIBM.SYSDUMMY1, 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.SYSDUMMY1 (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/

[Serge Rielau]

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/

[veffen]

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/