DB2 behaves differently through client and CLI

0
votes

I have faced this problem few times. So what I'm intrigued that why does DB2 behave differently while executing same commands from Squirrel and CLI. I'm working with Windows 10 and DB2 LUW version 9.7

This particular problem I'm facing is with with timestamp format:

This insert works when I run it from Squirrel but not from CLI:

INSERT INTO TABLE_A(CREATION_TIME) VALUES ({ts '2018-04-16 17:03:22.066000'})

I have generated the insert statement from Squirrel using "Copy as SQL INSERT-VALUES statement". The coltype is TIMESTAMP.

In general, there are other differences as well. For example statement REORG TABLE TABLE_A works only from CLI but not from Squirrel.

Could someone enlighten me where this different behavior derives from and is there some documentation which would help me to understand the phenomenom?

2
db2command-line-interfacesquirrel-sql
I think this documentation at least somewhat explains the context. Thought it does not explain (to me) why REORG or this specific weird looking timestamp-format is not working. – Julius Torkkeli

2 Answers

2
votes

The reason why REORG TABLE works at the command-line but not in Squirrel (jdbc) is that REORG TABLE is not SQL. jdbc applications run SQL, they don't directly run Db2-commands.

However, it is possible to run REORG indirectly via jdbc by using the stored procedure SYSPROC.ADMIN_CMD.

You can see documentation and examples here.

As regards timestamp and date literal values, the command-line (bash/ksh/db2cmd.exe/ or the interactive CLP) all accept valid timestamp literals. A timestamp literal for the command-line looks like this '2018-04-20-11.18.14.695148'

If you want to see the format on the command-line: use db2 values current timestamp or db2 values current date or db2 values current time while you are connected to a database.

Programming (scripting) at the command-line is suitable for administrators because you can freely mix both Db2-commands and SQL in the same script. You cannot do this directly in java, but only indirectly via stored procedures.

0
votes

The expression {ts '2018-04-16 17:03:22.066000'} is not valid SQL. But it's a valid escape sequence for the JDBC driver. That's why it works in SQuirrel (which is using JDBC) but not in the native client.

A portable, non-interface dependent way of writing a timestamp would be: 

timestamp '2018-04-16 17:03:22.066000'