I'm trying to restore a backup that is older than the current database. The restore went ok, but the roll forward didn't giving the following error: > SQL1265N The archive log file ".LOG" is not associated with the > current log sequence for database "" on node "0". I tried to roll forward to an earlier point in time, but it is required to restore to the end of the logs. The backup is from another server but it's actually a duplicate (so name etc. are the same.) What steps can I take to resolve this?

I've followed this simple guide and I was able to install DB2 on Docker. I now would like to import a sample database to play with. So I open IBM Data Studio, I right click on the instance > New database... , I fill up with my user name and password but then an error appears: > The system cannot verify the instance that you specified. Either enter > a valid instance name or click on the button next to the Instance name > field and select one from the list. > com.ibm.datatools.adm.explorer.ui.model.InstanceException: An internal > error occurred during: "Verifying instance db2inst1 in system > 192.168.1.44.". If I expand the error for more details I see: > 'Verifying instance db2inst1 in system 192.168.1.44.' has encountered > a problem. > > An internal error occurred during: "Verifying instance db2inst1 in > system 192.168.1.44.". > > An internal error occurred during: "Verifying instance db2inst1 in > system 192.168.1.44.". > > com.ibm.db2.das.core.DasMessageIdentifier (initialization failure) A dive in the official documentation brings me to this solution but I don't think that could be the problem. What intrigues me is the 'dot' (.) after the IP address. Could this be the error? Or is there any other way to import a sample database from IBM Data Studio?

Currently we have several databases with a HADR configuration where the primary databases are on a Linux Server "A", with the standby all on Linux Server "B". DB2 version is 9.7. We are attempting to relocate the primary database of one of these databases (with the intent of moving all later) to a new Linux Server "C". Efforts to find something similar have just brought back results of HADR takeover, that is not what we are aiming to do. Lets call this database MYDB. I have taken the steps below, and while HADR will start between the two by issuing the relevant start HADR commands first on the standby then on the primary, issuing 'db2pd -db MYDB -hadr' is showing them as disconnected with 'S0000000.log' as the log file on the opposite end. The correct log is being displayed locally. **STEPS TAKEN** 1. Quiesced the database and then stopped HADR on the primary, confirmed on the secondary there was now a log gap, stopped HADR on the Standby and deactivated. 2. Took an offline backup on the current primary database and sent that to the new server "C", where an identical version of DB2 is already set up. 3. Created a new database 'MYDB' and restored from the backup sent over. 4. Updated the relevant database configurations: On new server C : >db2 update db cfg for mydb using HADR_LOCAL_HOST C >db2 update db cfg for mydb using HADR_LOCAL_SVC hadr_mydb_c On the existing standby B: >db2 update db cfg for mydb using HADR_REMOTE_HOST C >db2 update db cfg for mydb using HADR_REMOTE_SVC hadr_mydb_c >db2 update alternate server for database mydb using hostname c port 3700 'hadr_mydb_c' has been added to /etc/services on both 'B' and 'C' with a defined port of 3734 'C' has been added to both 'B' and 'C' hosts files. Log locations ect have been created to match the existing server 'A'. 5. At this stage we have done a db2stop and start on the backup, then when we issue 'db2 start hadr on db mydb as standby' we get a confirmation that HADR has started. 6. On the new primary we issue 'db2 start hadr on db mydb as primary' we again get a confirmation that HADR has started. db2pd shows HADR as active but the databases not connected. Issuing the following command on the backup instance to see HADR details I can't see the MYDB database listed along with the other databases.

"SELECT SUBSTR(DB_NAME, 1, 8) AS DBNAME, HADR_ROLE, HADR_STATE,HADR_SYNCMODE, HADR_CONNECT_STATUS,HADR_HEARTBEAT,HADR_TIMEOUT,HADR_LOG_GAP FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T"

Luckily reverting the HADR config back to what they were previously we can reconnect HADR between the old primary and backup. Any ideas of how best to proceed?

I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better way to do that. Query statement is like below: Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type" From Table X Where Column 1 = "Fly" Group by Column 1, Column 2 UNION ALL Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type" From Table X Where Column 1 = "Fly" Group by Column 1, Column 2 UNION ALL Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type" From Table X Where Column 1 = "Fly" Group by Column 1, Column 2 Thanks!

I have a table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | 5103,5106,5107,5108 And the result should return individual rows, like this: | ID | OtherID | Data +--------+-----------+-------------------------- | 5059 | 73831 | 5103 | 5059 | 73831 | 5107 | 5059 | 73941 | 5103 | 5059 | 73941 | 5104 | 5059 | 73941 | 5107 | 5059 | 73974 | 5103 | 5059 | 73974 | 5106 | 5059 | 73974 | 5107 | 5059 | 73974 | 5108 Basically, I need to split my data at the comma into individual rows. The result will be stored in a temporary table (Like: ID, OtherID, NewID). My version of DB2 is 9.7

I am trying to automate some things on DB2 and would like to use a batch file to do this. I create the following batch file test.bat: db2cmd - -w "db2 -tvf test.db2" I then try to do two commands in the test.db2 script, which work together in the CLP interactive mode: !set db2instance=INST1; !db2pd -db MYDB -hadr; I then get the following output: >Database MYDB not activated on database partition 0. Option -hadr requires -db or -alldbs option and active database. I'm using DB2 9.7 LUW on Windows 2008 R2.

There was a feature called DataLinks that allowed to access data in files from SQL. This was introduced in Db2 V5.2 and removed in V9.7. However, in v11.5 there is a "new" feature called external tables. What is the difference between these two approaches? - Data Links Manage - http://users.sdsc.edu/~jrowley/db2/Data%20Links%20Manager%20QB.pdf - External tables - https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r_create_ext_table.html

After changing the hostname of my db2 server, the db2 services won't start anymore. After reading the notes on how to rename the db2 hostname I understand what went wrong, some things should have been done before restarting the server. What are my options to fix in this scenario?

Suppose I have two tables, A and B. They "share" the same primary key, which is indexed in both tables. Table B also has a timestamp column which is separately indexed. I do a subselect on B using the timestamp column (I give it a 24-hour interval for the timestamp to belong to), and inner join the results against A using the primary keys. A and B are huge (O(10^9) rows). When I give the subselect a recent 24-hour period, it seems that the statistics are poor and the planner underestimates the number of rows coming out of the subselect (say, anywhere between O(1) and O(10^5) rows, while there are actually 6M such rows for the queries I run). It picks a nested loop join, with the subselect output as the "outer" table and A as the "inner" table, accessed via its index. While NL joins have a bad rep, it only takes a few minutes to complete. When I tell it to look for older timestamps ("old" 24-hour periods), it does understand that there are about 6M rows coming out of the subselect. It picks a hash join, with A as the outer/probe table, and the subselect output as the inner/build table. It does a table scan on A as part of the plan. It doesn't complete even after 48 hours. Is there a way to force or hint to the planner that it should use the nested loop join? While I'd be interested in hearing about DB tuning as a solution, this is a box I have no authorization to tune. ...more information: the join switches from nested loop to hash when the number of rows returned by the subselect increases beyond about 1.012*10^6.

I have a DB2 table where one of the columns is encrypted using the two parameter variant of [ENCRYPT](https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0004211.html?cp=SSEPGG_9.7.0%2F2-10-3-2-51) (so the password is supplied in the SQL itself). Unfortunately due to an ETL issue the column got populated by data with two different encryption keys. I want to fix this, by running some kind of script, but unfortunately I cannot do a mass update as DECRYPT_CHAR will throw an exception on the first row where it couldn't use my encryption key. Is there a way to write a SQL script that would go through all of the rows and re-encode the rows which use the wrong encryption keys without throwing an exception? I'd prefer plain SQL if possible.

I *have* looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries - most usually fail, the remainder provide garbage results. NOTE THAT THIS IS FOR IBM DB2 v9.7! *NOT* SQL Server or anything newer. I have a single table that contains quasi-distinct rows, but has no key. The odds of finding a duplicate are next to none based on ALL the fields, and the probability of duplication increases with the fewer fields selected in the query. I'm mostly ok with that at this point. There are more fields than listed below, and I have added a key just for reference... ###PSEUDO-QUERY

select sum(max(NumImages)) from table where Source in ('A','B') and Event='190'

###TABLE Key ID NumImages Source Event 1 1 4 A 190 2 1 - - 247 3 1 2 B 190 4 1 - - 134 5 2 7 C 190 6 2 2 A 190 7 2 1 B 190 8 2 5 A 190 9 3 5 A 190 10 3 - - 247 11 3 - - 134 12 3 - - 815 13 4 2 A 190 14 4 9 A 247 15 4 5 B 190 16 4 4 B 134 ###PARAMETERS

Source in ('A','B') 
Event = '190'
NumImages is not null

(*I don't think that will ever happen with the preceding parameters) ###OUTPUT ID Sum 1 6 2 6 3 5 4 7 Because: * 1 = 6 ; keys 1 and 3 are both max for event 190 and source A or B * 2 = 6 ; key 8 (5 - max for Event 190 Source A) + key 7 (1 - max for Event 190 Source B). Source C (key 5) is ignored. * 3 = 5 ; key 9 is the only value matching the parameters * 4 = 7 ; keys 13 and 15 are the only ones that match Event 190, even though Source and NumImages have values that match against the other parameters.

Using DB2 9.7, is it possible to produce a result set where the column-headers contain line-breaks? Something like: select field_name as "Line1
Line2" from table_name I want the column-header to look like this: Line1 Line2 However, this is not for HTML. Instead I'm wanting it to look this way when I run the query (raw) in my sql editor (Dbeaver ) and in the CSV file I export from Dbeaver (so that the columns are narrower -- to prevent horizontal scrolling while viewing the result-set).

I am getting below error in db2 when executing the sql query- > SQL20448N "05/09/2016" cannot be interpreted using format string > "YYYY-MM-DD" for the TIMESTAMP_FORMAT function. here is my sql qyery- SELECT DLR_CD, FIN , YEAR(TO_DATE(CURRENT_DATE ,'YYYY-MM-DD'))-YEAR(TO_DATE(CRTNG_DTE,'YYYY-MM-DD')) AS AGE FROM ASPECT.RO_CAR_PARK_EXTRACT WHERE YEAR = PARM_YEAR what could be the reason for this? Database - DB2 v9.7.200.358 Platform- home basic 7

I need to read the DB2 DAS Parameters via any method other than DB2 Command "get admin configuration". I need to read the parameters like EXEC_EXP_TASK or SCHED_ENABLE from file or database. I cannot find any reference of any files on DB2 where these parameters would be available or any table from where I can fetch these parameters via select query. Any Pointers?

I want to catch exception in DB2 function and return 0 if there was an exception. I don't know how the right syntax create function is_decimal(c_data varchar(100)) RETURNS INTEGER begin select cast(c_data as decimal(12,10)) from sysibm.sysdummy1; return 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING return 0; end

I have a DB2 database and I'm using Control Center. I've found a procedure in the 'Stored Procedures' folder, which I need to modify. In MS SQL Server and Oracle you can accomplish this right from the DBMS. Is there a way I can modify this procedure with an editor in DB2 (9.7.0), similar to SQL Server and Oracle?

I'm trying to set driver properties like: * clientAccountingInformation * clientApplicationInformation * clientProgramId * clientProgramName in Squirrell 3.6 (http://squirrel-sql.sourceforge.net/) . See https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052607.html for a more detailed description of these properties. I was under the impression that it should be possible to inspect these values with for example: select APPLICATION_HANDLE, APPLICATION_NAME, APPLICATION_ID , CLIENT_WRKSTNNAME, CLIENT_ACCTNG, CLIENT_USERID , CLIENT_APPLNAME from table(MON_GET_CONNECTION(cast(NULL as bigint), -2)) but no matter what values I use, I get null back from the query. The db cfg regarding metrics are: Request metrics (MON_REQ_METRICS) = BASE Activity metrics (MON_ACT_METRICS) = BASE Object metrics (MON_OBJ_METRICS) = BASE Unit of work events (MON_UOW_DATA) = BASE Lock timeout events (MON_LOCKTIMEOUT) = HISTORY Deadlock events (MON_DEADLOCK) = HISTORY Lock wait events (MON_LOCKWAIT) = HISTORY Thoughts anyone, what am I missing?

DB is in Archive Mode. We are creating full Online Backups every night at 23:00. We set automatic removal of old online backups and archive logs. AUTO_DEL_REC_OBJ ON NUM_DB_BACKUPS 2 REC_HIS_RETENTN 2 In backup folder we have two online backups and that is ok: Backup1 22.02 23:00 Backup2 23.02 23:00 In the archive logs folder we have these logs: from S0000047.LOG to S0000057.LOG (from 22.02 23:00 to the 23.02 22:41) from S0000058.LOG to S0000062.LOG (from 23.02 23:00 to this morning 24.02) Those are OK logs of course. But I noticed that we have also archive logs which should not be there (logs after the backup of 21.02 which is already deleted and all those transactions are already contained within the backup of 22.02). But we have archive logs from S0000037.LOG to S0000046.LOG (from 22.02 01:41 to the 22.02 22:42) So those are unnecessary logs. On some other system (server) for some other customer we have also enabled this purging but there we have regular situation - only backups and archive logs after those backups are kept, there are no previous archive logs, but on this system it seems like deletion of all archive logs is late to 1 day. Why is that I really think this is not regular situation (as I said also based on experience on some other server)