Hot answers tagged

4

The error message is very clear. I suppose the type of CRTNG_DTE is text/varchar and at least one of the values is not in the YYYY-MM-DD format (05/09/2016 clearly isn't!) and the conversion TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') fails. The only unclear in the message is the mention of TIMESTAMP_FORMAT() function. This part of the DB2 docs, function TO_DATE() ...


4

I believe you can do so through IBM Data Studio. I would use that tool anyway as that is now the approved IBM tool for interacting with DB2 and Control Center is deprecated.


3

I think you want multiple aggregations: First find the max for each ID-Source combination, then sum for every ID: select ID, sum(MaxNumImages) as SumMaxNumImages from ( select ID, max(NumImages) as MaxNumImages from table where Source in ('A','B') and Event = '190' and NumImages is not null group by ID, Source ) as agg group ...


3

DB2 will allow you to use a new line character in a quoted identifier: $ db2 'select ibmreqd "foo > bar" from sysibm.sysdummy1' foo bar ------- Y 1 record(s) selected. The question is, will Dbeaver let you enter a new line character where you want it.


2

I came up with a solution for your data set based on some work that I was doing and some modifications to the solution posted by Serge Rielau and Rick Swagerman on IBM's developerWorks. Data Setup queries: DECLARE GLOBAL TEMPORARY TABLE sample_data (id INTEGER, otherid integer, data VARCHAR(255)) WITH REPLACE ON COMMIT preserve rows NOT logged; INSERT INTO ...


1

You can always clean them up manually or use a script to clean them up (maybe looking for files older than seven days or something).


1

You could just try applying those steps. If that fails, another option would be to create a new instance and then restore the database(s) over to the new instance. Another option could be uninstalling the instance and then reinstalling it. Another could be taking a backup of the database, saving it off somewhere and then doing an image restore (pending ...


1

Hash joins require an "equijoin predicate". So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.


1

Like @mustaccio pointed out, you need to select into something, and you need to declare the handler before the actual code. Something like: create or replace function is_decimal(c_data varchar(100)) RETURNS INTEGER begin declare t decimal(12,10); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN return 0; END; select cast(c_data as ...


1

Both commands in test.db are Windows commands, you don't need the CLP to run them. Try: db2cmd -w "set db2instance=INST1 & db2pd -db MYDB -hadr" Your original attempt did not work you invoked Windows commands from the DB2 CLP: The first one created a new environment and set the DB2INSTANCE variable in it, then quit, which destroyed that environment. ...


1

I found a way to do this using two batch files: test1.bat ------------------------------------------------------------------------ db2cmd -c -i -w "test2.bat" PAUSE test2.bat ------------------------------------------------------------------------ set db2instance=INST1 db2pd -db MYDB -hadr This is effectively the same as running in interactive mode but if ...


1

Yes there is. You could do this with SQL PL to create a stored procedure to do this. Or if you prefer other script languages you could use KSH, BSH, or even Perl. The point being to catch the errors or exceptions (a little easier to do with SQL PL IMHO) and then you can react to them the way you wish.


1

It appears to be a bug in the way Squirrel handles additional parameters. Data Studio acts as expected with both db2jcc and db2jcc4 drivers. I have filed a bug to: https://sourceforge.net/p/squirrel-sql/bugs/1201/


Only top voted, non community-wiki answers of a minimum length are eligible