DB2 Table Reorg - DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=7;

I got db2 error message when I ran a web application.

 DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=7;   

After doing some research, I found out I need to reorg the table it mentioned in the error message.

I tried to just run the reorg command in Aqua Data Studio, but I got error message. After consulted with our DBA, this reorg command is not a sql command. The way to run it in Aqua Data studio is as below.

 call sysproc.admin_cmd('reorg table TABLE_NAME')  

How to get last inserted id in DB2?

I have two tables in db2, one child, one parent. I would like to insert data into one table, and get the id (which auto generated in the db2,) then insert the id into another table as a foreign key by using ETL tool, Talend open studio.

After a long time research I found out a solution.
Within the same connection and same transaction use tDB2Input with this select:

 SELECT Identity_val_Local() as id FROM sysibm.sysdummy1  

The Talend job is like this:

 

Select one unique record from DB table with the latest date

I have a file_store table like below.

I would like to select the latest records for each filetype_id.

The SQL to do this can be like this.

 SELECT f.file_id,f.FILETYPE_ID, f.file_obj_l,max_date  
 FROM FILE_STORE f inner JOIN   
 (SELECT FILETYPE_ID, max(f1.UPDATETS) as max_date FROM FILE_STORE f1  
 where FILETYPE_ID in (1, 2, 3)   
  group by FILETYPE_ID  
 ) a  
 on a.FILETYPE_ID = f.FILETYPE_ID and a.max_date = f.UPDATETS