Start a new topic

How do I capture SQL warnings issued by DB2?

[This topic is migrated from our old forums. The original author name has been removed]

I'm using DbVisualizer 5.0.0.3 (licensed) against a DB2 database using a JDBC/ODBC driver. I'm running a create table statement which runs fine and creates the table. When a colleague runs the same query from the official DB2 query tools they are seeing a warning issued because the USER system value is a VARCHAR(128). CREATE TABLE MYSCHEMA.MYTABLE ( some_date DATE NOT NULL, -- audit fields last_op_ind CHAR(1) NOT NULL WITH DEFAULT 'I' constraint CC_RFVE_1 CHECK (LAST_OP_IND IN ('I','U','D')), last_op_by CHAR(8) NOT NULL WITH DEFAULT USER, last_op_date_time TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ); By convention, our environment always inserts the current USER into the last_op_by field. I'm making this change so that DB2 handles that by default. There was some confusion as DBVis didn't report the warning, or at least, I didn't see the warning. We know that errors are clearly reported but what about warnings on successful statements? Is this just a driver issue or do I need to make some setting changes to pick these up?

[This reply is migrated from our old forums. The original author name has been removed]

Re: How do I capture SQL warnings issued by DB2?
> Mark, > > It may be driver dependent whether the warning is > handled properly or not. I've just set my system up to use the DB2 jdbc driver and I am getting the warning messages back, so yes, this is driver-dependent behaviour. Thanks for your efforts.
[This reply is migrated from our old forums.]

Re: How do I capture SQL warnings issued by DB2?
Mark, It may be driver dependent whether the warning is handled properly or not. I tested with this config: Product: DbVisualizer Personal 5.0.0.6 Java VM: Java HotSpot(TM) Client VM Java Version: 1.5.0_06-64 Java Vendor: "Apple Computer, Inc." OS Name: Mac OS X OS Arch: i386 OS Version: 10.4.7 DB2/LINUX SQL08020 IBM DB2 JDBC Universal Driver Architecture 2.8.46 DbVisualizer simply asks the JDBC driver for any SQL Warnings just after the execution is finished. Having [b]retrieveMessagesFromServerOnGetMessage[/b] set or not doesn't show any difference here. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: How do I capture SQL warnings issued by DB2?
When running the create statement I should see what you see but my log entry just shows me: 09:20:29 [CREATE - 0 row(s), 1.828 secs] Command processed but no rows were affected ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 1.828/0.000 sec [0 successful, 1 warnings, 0 errors] It doesn't include the warning itself. I have the "retrieveMessagesFromServerOnGetMessage" flag set to "true". Is there anything else I need to set? I'd expect it to run fine on insert, that's okay. I've attached a screenshot showing DBVis straight after running the sql.
[This reply is migrated from our old forums.]

Re: How do I capture SQL warnings issued by DB2?
Mark, I am not really sure if you mean that the warning should be displayed at create of the table or at insert? Just tried and I get a warning when creating the table: 19:01:47 [CREATE - 0 row(s), 0.298 secs] Command processed but no rows were affected Code: 20114 SQL State: 01642 --- Column "LAST_OP_BY" in table "MYSCHEMA.MYTABLE" is not long enough for the defined length of the USER default value. ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.298/0.000 sec [0 successful, 1 warnings, 0 errors] But I don't get any at insert. Please clarify. Regards Roger