snapshot tbsp table functions via jdbc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

veffen - 04 Jul 2006 16:06 GMT

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

Reply to this Message

Hardy - 04 Jul 2006 16:19 GMT

SELECT * FROM TABLE(SNAP_GET_TBSP(CAST(NULL AS VARCHAR(1)-!--)--!--,
-1)) AS T

veffen

> Hello all,
>
[quoted text clipped - 21 lines]
>
> Hans

Reply to this Message

veffen - 05 Jul 2006 15:27 GMT

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
[quoted text clipped - 26 lines]
> >
> > Hans

Reply to this Message

Gregor Kovač - 05 Jul 2006 16:53 GMT

> Hello,
>
[quoted text clipped - 50 lines]
>> >
>> > Hans

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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Serge Rielau - 05 Jul 2006 18:02 GMT

Are you playing with unicode tables in a non unicode database?

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

veffen - 07 Jul 2006 08:56 GMT

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,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?
>
[quoted text clipped - 5 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Serge Rielau - 07 Jul 2006 12:40 GMT

> Thanks for your answers.
>
[quoted text clipped - 54 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Serge Rielau - 07 Jul 2006 12:42 GMT

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.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

veffen - 07 Jul 2006 12:59 GMT

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
[quoted text clipped - 7 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message