DB2 things I've learned To count the number of patents in the database for a given week, 1) Logon or su to inst1, 2) db2 connect to pas0401 user inst1 using inst1_password or db2 connect to patent user inst1 using inst1_password 3) db2 "select count(*) from main where isd='1997-08-05'" 4) db2 "select patn,cdlabel,image_pages from inst1.imag where patn='US04984298__'" or db2 "select patn,cdlabel,insert_ts from inst1.imag where patn like 'EP00025%'" Underscore (_) = Exactly one character Percent Sign (%) = Zero or more characters or db2 "select patn,cdlabel,image_pages from inst1.imag where image_pages > 500" or db2 "select a.patn from inst1.main a, inst1.imag b where b.image_pages=1 and a.patn=b.patn and substr(a.patn,1,2) = 'WO' group by a.patn" This was to find the smallest image we've got, with an entry in main. There were a lot of 1-pagers in imag, but not in main. The smallest I found were 2-pagers. Note with substr, the first character is 1 and the second arg is the byte count, so substr(patn,1,2) = the country code and substr(patn,11,2) is the kind. If you change this by adding say, ",a.datasrc,b.image_pages" to the select, i.e. db2 "select a.patn,a.datasrc,b.image_pages ... then you need to add ",a.datasrc,b.image_pages" to "group by" at the end. The rules of the game are, any fields you have listed in the "select" clause, must be specified in the "group by" clause. db2 "select patn,count(*) from inst1.imag group by patn having count(*)>1" This was to find all patents that have multiple images in the imag table. E.G. In JAPIO, EP00438662A3, or in US, US03429352__, both of which had 2. 5) db2 "describe table inst1.imag" if you don't know the field names. 6) db2 "update imag set IMAGE_PAGES=344 where patn='EP01033405A2' and cdlabel='mepa2000045'" 7) db2 "select isd,count(*) as count from main where year(isd)=2002 and datasrc='USG' group by isd" or db2 "select isd,datasrc,count(*) as count from main where year(isd)=2002 group by isd,datasrc" or db2 "select isd,datasrc,count(*) as count from main where year(isd)=2002 and datasrc='USG' group by isd,datasrc" 8) To see what the various formats of Japanese patents are, db2 "select substr(patn,1,4),substr(patn,11,2),count(*) from main \ where patn like 'JP%' group by substr(patn,1,4),substr(patn,11,2)" To see your connection state, db2 get connection state To drop your connection, db2 connect reset - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - One time I was deleting a week's worth of patents and got stuck after deleting about 2100 out of the 2600 I was doing. DB/2 just hung. A control-c didn't get me out. Alex said it was DB/2 getting in a deadlock situation. To get out of it, 1) db2 force application all 2) db2stop 3) Wait a minute or so 'till what's going to die, dies. 4) If things still haven't broken loose, look at output from ipcs | grep inst1 Shouldn't be any, but there may be dozens of lines. The lines that start with "s" are semaphores, with "m" are shared memory segments, and with "q" are message queues. Start killing the "q" ones, the message queues with ipcrm -q ##### where ##### is the second number of the ipcs output. To restart db2 again after all that, 1) db2start 2) db2 activate db patent To validate the db2 database is ok, as inst1, 1) /dfs/patent/util/show_tspace 2) db2 connect to patent db2 "select * from main where patn='5551212'" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The time I accidently loaded 1997-08-05's data twice, to remove both sets of 1997-08-05 patent data, db2 "delete from main where isd = '1997-08-05' " - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The time I accidently (again) mistyped the slot number, db2 "update main set opti_cd = '126A' where patn='D382685'" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When I did this on loon, things went ok, but when I did this on a205 in Southbury, I got the msgs DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0964C The transaction log for the database is full. SQLSTATE=57011 To fix this, you need to change the 3 DB2 settings, LOGFILSIZ, LOGPRIMARY, and LOGSECOND. To see what they are now, type db2 get db cfg for patent About 10 lines up from the bottom are these lines. Log file size (4KB) (LOGFILSIZ) = 2000 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 2 ... Path to log files = /home/inst1/inst1/SQL00001/SQLOGDIR/ To change the settings, type db2 update db cfg for patent using LOGFILSIZ 6000 LOGPRIMARY 6 LOGSECOND 5 Then stop and restart the database. db2stop If the database doesn't come down, try db2 force applications all or if that isn't enough, db2stop -kill Then db2start - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When I tried the above, I then got SQL1004C There is not enough storage on the file system to process the command. when just doing the db2 connect to patent. The problem there was there was only 96MB free in /home and we were trying to get 144MB (6 primary logs times 6000 4K-pages each). I changed the settings to db2 update db cfg for patent using LOGFILSIZ 4000 LOGPRIMARY 6 LOGSECOND 5 which should be 96MB. Should just fit ... It did. There *was* 96320KB free in /home, but after the db2 connect to patent, there was just 152KB free. Talk about cutting it close! - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I then got the msg DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0984C COMMIT or ROLLBACK was not successful. Subsequent SQL statements cannot be processed. SQLSTATE=58005 Then it took forever (an hour maybe? I dunno, I did something else) to do the db2 connect again. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Bruce gave /home another 132MB, so I now have 199MB to play with. Changed parms back to db2 update db cfg for patent using LOGFILSIZ 6000 LOGPRIMARY 6 LOGSECOND 5 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Still had problems, so wound up doing a bunch of db2 "delete from main where patn = 'D381785' " commands, created from the main.ld load file. See my wonderful build_SQL_commands_from_list.sh script, designed to assist in this situation. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The commands to catalog a database node are su - inst1 db2 list node directory Which returns stanzas similar to Node 2 entry: Node name = AS0201 Comment = Protocol = TCPIP Hostname = as0201e1 Service name = db2_inst1 db2 uncatalog node AS0201 db2 "catalog tcpip node AS0201 remote as0201e1 SERVER db2_inst1 REMOTE_INSTANCE inst1" Generically, this is db2 "catalog $protocol node $node_handle remote $real_IP_name SERVER $server_port_name REMOTE_INSTANCE $userid" where protocol=tcp/ip, node_handle is only used to tie a database alias to this node, real_IP_name is the DB/2 server I.P. name, server_port_name must exist in this client's /etc/services. We use 3700. and userid is generally inst1. You have to define the nodes before you define the database aliases. For the database alias, use su - inst1 db2 list db directory Which returns stanzas similar to Database 3 entry: Database alias = PAS0201 Database name = V4PROD Node name = AS0201 Database release level = 8.00 Comment = Directory entry type = Remote Authentication = SERVER Catalog node number = -1 db2 uncatalog db PAS0201 db2 catalog db V4PROD as PAS0201 at NODE AS0201 AUTHENTICATION SERVER Generically, this is db2 catalog db $database_name as $connect_handle at NODE $node_handle AUTHENTICATION SERVER where database_name is the name the server knows the database as, connect_handle is what's used when you say "db2 connect to pas0201" say, and node_handle is what was used in the node definition above. To test the above, see if you can connect to one of the DB/2 aliases, e.g. db2 connect to pas0205 user inst1 using inst1_password - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To see how a table is defined and what index are on it, type db2look -d patent -a -e -m -t main (there're also "-o" and "-u inst1" options) Kevin & I did this on 10-19-2000 and found that the unique keys for main were PATN & DATASRC, which these lines told us. -- DDL Statements for primary keys on Table "INST1 "."MAIN" ALTER TABLE "INST1 "."MAIN" ADD CONSTRAINT "MAIN_PATN_DS_ICNT" PRIMARY KEY ("PATN", "DATASRC"); Another time I did this for legalstatus and saw two indexes, -- DDL Statements for indexes on Table "INST1 "."LEGALSTATUS" CREATE INDEX "INST1 "."WIZ195" ON "INST1 "."LEGALSTATUS" ("LEGALCODE" ASC, "CN2" ASC, "LYEAR" ASC, "DOCTYPE" ASC, "DOCNR" ASC); -- DDL Statements for indexes on Table "INST1 "."LEGALSTATUS" CREATE INDEX "INST1 "."WIZ386" ON "INST1 "."LEGALSTATUS" ("LEGALCODE" ASC, "CN2" ASC, "GAZETTEDAT" ASC, "LYEAR" ASC, "DOCTYPE" ASC, -- DDL Statements for primary key on Table "INST1 "."LEGALSTATUS" ALTER TABLE "INST1 "."LEGALSTATUS" ADD CONSTRAINT "LEGALSTATUS" PRIMARY KEY ("CN2", "DOCTYPE", "DOCNR", "LEGALCODE", "PROCESSDAT", "DATASRC"); So I thought that searches with just DOCNR, e.g. db2 "select * from legalstatus where docnr='05551212' " would go quickly. They didn't. So I then tried CN2 and DOCNR. db2 "select * from legalstatus where cn2='US' and docnr='5551212' " It still took forever. Eric says that I needed to NOT skip columns within index definitions. If I want to search on CN2 and DOCNR, that I needed to also add DOCTYPE 'cause DOCTYPE is inbetween CNT and DOCNR in the primary key definition. (I guess the primary key is considered an index ???) db2 "select * from legalstatus where cn2='US' and doctype='P' and docnr='05551212'" came back instantly. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Here's generally how to backup a database using ADSM. See also, some notes in my aixnotes/adsm file for details on configuring the proper dsm.opt and dsm.sys files, in order for this to work. To backup the JAPIO Net Commerce database for example, on ips03i as inst1, db2 deactivate db ncjapan db2 "backup database ncjapan use adsm" db2 activate database ncjapan To query the backups made to ADSM, you can use the db2adutl utility, installed at /usr/lpp/db2_06_01/adsm/db2adutl, but when one "creates an instance", it gets copied to the /sqllib/bin directory, for example, /home/inst1/sqllib/bin/db2adutl. On elephant, userid ipnnc has backed up the Net Commerce database to TSM, so you can su - ipnnc db2adutl query Query for database IPNNC Retrieving full database backup information. full database backup image: 1, Time: 20010214051720, Oldest log: S0000000.LOG, Sessions used: 1 full database backup image: 2, Time: 20010129045556, Oldest log: S0000000.LOG, Sessions used: 1 Retrieving tablespace backup information. No tablespace backup images found for IPNNC Retrieving load copy information. No load copy images found for IPNNC Retrieving log archive information. No log archive images found for IPNNC To restore from one of these backups, you use that 14-digit timestamp, e.g. su - ipnnc db2adutl extract full taken at 20010214051720 - - - - - - - - - - - - - - - - - - - - - Another backup query command is db2 list history backup all for patent - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To view the settings for a database, as inst1 on the DB/2 server, db2 get db cfg for patent Some interesting lines are Path to log files = /home/inst1/db2logs/v4prod/ Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = ON or for Japan's Net Commerce, db2 get db cfg for ncjapan | egrep 'Path to log|LOGRETAIN|USEREXIT' Path to log files = /home/inst1/db2logs/ncjapan/ Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = ON Note the state of LOGRETAIN & USEREXIT. Since USEREXIT is on, DB/2 will look for the db2uext2 program in inst1's PATH (perhaps in /home/inst1/sqllib/bin?), and run that. The expectation is, that user exit will erase the log files. There are sample files, e.g. /home/inst1/db2backup/tools/c/db2uext2.adsm or is it db2uext2.cadsm? In March, 2001 and again in September, 2001, we saw these settings, but did not see any db2uext2 program, and there were months of accumulated log files under /home/inst1/db2logs -- 108 for patent and 83 for ncjapan. We fixed this back in March, but we need to get them to change this again now. To change the settings, type db2 update db cfg for patent using USEREXIT OFF Then stop and restart the database. db2stop If the database doesn't come down, try db2 force applications all or if that isn't enough, db2stop -kill Then db2start ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ On 1-28-2002, the weekend after the DB/2 Version 6 to Version 7 upgrade, db2 get db cfg for patent | egrep 'Path to log|LOGRETAIN|USEREXIT' showed Path to log files = /home/inst1/db2logs/v4prod/ Log retain for recovery enabled (LOGRETAIN) = RECOVERY User exit for logging enabled (USEREXIT) = OFF First active log file = S0000244.LOG Sigh! Again, it has been changed. There were 178 log files, ranging from -rw------- 1 inst1 dbadmin1 32776192 Oct 31 22:53 S0000070.LOG -rw------- 1 inst1 dbadmin1 22941696 Jan 20 10:06 S0000199.LOG -rw------- 1 inst1 dbadmin1 1966080 Jan 27 22:54 S0000210.LOG -rw------- 1 inst1 dbadmin1 32776192 Jan 29 05:48 S0000211.LOG -rw------- 1 inst1 dbadmin1 32776192 Jan 29 08:11 S0000244.LOG -rw------- 1 inst1 dbadmin1 32776192 Jan 29 08:29 S0000246.LOG I erased 130 of them, 30 S00000*.LOG and 100 S00001*.LOG. Now what did we do back in September/October, 2001? --- Oh, I have a big note from 10-3-2001, and back then, I was wrong. Let's see, ... Ahhh, I told them that LOGRETAIN=YES (which is the same as RECOVERY, I presume), IS correct. But I also told them that they should periodically erase old log files by getting and implementing dg_prune, available from the Internet. This is what was never implemented. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - =========== This came from my aixnotes/verity file ======================= Permissions on DB/2 tables are kept in the syscat.tabauth table. The description (evidently changed from what is documented in the DB/2 book, is Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ----- GRANTOR SYSIBM VARCHAR 128 0 No GRANTEE SYSIBM VARCHAR 128 0 No GRANTEETYPE SYSIBM CHARACTER 1 0 No TABSCHEMA SYSIBM VARCHAR 128 0 No TABNAME SYSIBM VARCHAR 128 0 No CONTROLAUTH SYSIBM CHARACTER 1 0 No ALTERAUTH SYSIBM CHARACTER 1 0 No DELETEAUTH SYSIBM CHARACTER 1 0 No INDEXAUTH SYSIBM CHARACTER 1 0 No INSERTAUTH SYSIBM CHARACTER 1 0 No SELECTAUTH SYSIBM CHARACTER 1 0 No REFAUTH SYSIBM CHARACTER 1 0 No UPDATEAUTH SYSIBM CHARACTER 1 0 No To query it, db2 "select substr(grantor,1,11),substr(grantee,1,11),substr(tabname,1,11),SELECTAUTH\ from syscat.tabauth where tabname='FAMI'" Do a db2 list tables or db2 list tables for all on marge to see the possible tables and notice that the tabname column must be given in upper case. To fix the above DB/2 bind problem, I had to minimally, db2 "grant select on fami to ipsadmin" and db2 "grant select on oabs to ipsadmin" but the correct full permissions are db2 "grant alter,delete,index,insert,select,references,update on fami to ipsadmin" and db2 "grant alter,delete,index,insert,select,references,update on oabs to ipsadmin" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - In Japan, we saw there were 108, 32 MB log files in the /home/inst1/db2logs/v4prod/ directory. A "db2 get db cfg for patent" command, showed Log file size (4KB) (LOGFILSIZ) = 8000 Number of primary log files (LOGPRIMARY) = 8 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /home/inst1/db2logs/v4prod/ First active log file = S0000150.LOG Group commit count (MINCOMMIT) = 1 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = ON Of particular interest, is the last two lines. LOGRETAIN=OFF says to erase old log files, i.e. have a circular log, which is what we want, but because USEREXIT is ON, DB/2 won't erase log files. The presumption is that this user exit will do that. Perhaps, you decide to archive log files to ADSM/TSM before erasing, for example. In DB/2, the name of this user exit is db2uext2, which we didn't find anywhere in inst1's PATH. To change it, we would type "db2 update db cfg for patent using USEREXIT OFF". This also had to be done for/to the ncjapan database. We also decided to increase the number of secondary log files, i.e. "db2 update db cfg for patent using LOGSECOND 100". This allows us to do transactions that require up to 108, 32MB log files, for example, deleting all rows from some table (which we were trying to do earlier this week, but when the "db2 delete" failed due to insufficient log space, we decided dropping the table & recreating, was quicker and easier). - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To escape one of the DB/2 special characters in a query, for example, to search for a real underscore, type in db2 "select * from inst1.main where patn like 'US0D%/_/_' ESCAPE '/'" You can specify any escape character you wish. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Here was a neat query that Kevin showed me. In Japan, there was some bad data in the PNO10 column of uref. I wanted to know where this data came from. So for all the PATN's in inst1.uref where PNO10 is like 'USRE%', give me all the distinct TEXT_CD's for those uref.patn's, from inst1.main. db2 "select distinct text_cd from inst1.main where patn in (select distinct patn from inst1.uref where pno10 like 'USRE%')" This returned TEXT_CD --------------- usa01week35 usa01week36 usa01week37 usa01week38 usa01week39 usa01week40 which proved that all this bad data is coming from the US converters. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To see what connections DB/2 has open, and which are busy, first db2 connect to patent Then db2 list applications or db2 list applications show detail The states I've seen are UOW Waiting = This Unit of Work is Waiting, i.e. it's idle. Connect Completed = Also idle. I don't understand the distinction. Compiling = The pre-cursor to executing. UOW Executing = It's busy. One useful filter to see just the busy applications, is db2 list applications show detail | egrep -v 'UOW Waiting|Connect Completed' | cut -c1-133 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Some DB/2 commands to insert, modify, and delete data. db2 "insert into inst1.vol values('A','usa01week40','A','A','2001-01-20')" or db2 "insert into imag (patn,image_pages,cdlabel) values('WO09964046A1',0,'IPN-SBY')" db2 "update vol set cd_status='R' where cdlabel='usa01week40'" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - User-defined functions are stored in syscat.functions. db2 "select * from syscat.functions where funcschema='INST1'" or db2 "select * from syscat.functions where funcname='NPOPATN'" Back on 2-28-2001, Paul had created two user-defined functions, npopatn & noblank, used when extracting data from the IPN database, for a NPO site. I found them on elephant in the /home/inst1/sqllib/function directory. I copied the source & compiled java files into ~jasper/aixnotes/db2_examples. See -rw-r--r-- 1 jasper dbadmin1 1346 Nov 30 14:50 DELPHUdf.class and -rw-r----- 1 jasper dbadmin1 3386 Nov 30 14:50 DELPHUdf.java Thankfully, Paul also left behind a small regudf.sql file that showed how to remove, re-add, and use the two functions. Here are the contents of that file. connect to patent; drop function npopatn; drop function noblank; create function npopatn(varchar(12)) returns varchar(12) fenced no scratchpad returns null on null input variant no sql no external action language java parameter style db2general no final call allow parallel dbinfo external name 'DELPHUdf!npopatn'; commit work; create function noblank(varchar(127)) returns varchar(127) fenced no scratchpad returns null on null input variant no sql no external action language java parameter style db2general no final call allow parallel dbinfo external name 'DELPHUdf!noblank'; commit work; export to aa.del of del select npopatn(patn), npopatn(patn10) from main where datasrc='USG' and substr(patn,1,3)='USP' fetch first 10 rows only; commit work; export to bb.del of del select Rtrim(Ltrim(icl)),noblank(icl) from clas_icl where datasrc='USG' and substr(patn,3,1)='D' fetch first 10 rows only; connect reset; To add this to dephds059's system, I did login to inst1 on elephant cd /home/inst1/sqllib/function scp -p DELPHUdf.java inst1@dephds059:/home/inst1/v4/java scp -p DELPHUdf.class inst1@dephds059:/home/inst1/v4/java scp -p regudf.sql inst1@dephds059:/home/inst1/v4/java Then as inst1 on dephds059, cd /home/inst1/v4/java db2 -cvtf regudf.sql This worked in that it added the two functions, but it failed to do the test cases. For a simple test case, this command db2 "select patn,npopatn(patn) from main where patn='US05551212__'" on elephant produces PATN 2 ------------ ------------ US05551212__ US05551212__ US05551212__ US05551212__ 2 record(s) selected. But on a DB/2 Version 7 database (trantor, dephds059-61), we get PATN 2 ------------ ------------ SQL10003C There are not enough system resources to process the request. The request cannot be processed. SQLSTATE=57011 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To do a runstats on a database, type db2 "reorgchk update statistics on table all" Warning, this takes hours to run !!!! This improves database performance by updating statistics that DB/2 maintains for each table. This allows the DB/2 optimizer to make the right choices when planning how to do an operation. Perhaps better is to run runstats for each table individually, like so db2 "runstats on table inst1.main with distribution and detailed indexes all shrlevel change" The advantage of doing it this way, is in that "shrlevel change", which does NOT lock the database as the above reorgchk command does. The problem with this though, is you have to run this for each table. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To query when the last time a runstats was run on a table, try db2 "select substr(tabschema,1,11) as tabschema,substr(tabname,1,11) as tabname, \ type,stats_time from syscat.tables where tabschema='INST1' and type='T'" In Patolis on 8-20-2002, this returned the following, showing that they don't do periodic runstats on their database. TABSCHEMA TABNAME TYPE STATS_TIME ----------- ----------- ---- -------------------------- INST1 BSUM T 2002-02-01-05.58.14.754836 INST1 CLMS T 2002-02-01-06.18.09.549015 INST1 COUNTRY T 2002-02-01-06.18.09.900920 INST1 DRWD T 2002-02-01-07.10.20.672407 INST1 GOVT T 2002-02-01-07.10.53.458584 INST1 PUBL T 2002-02-01-08.06.34.575051 INST1 RLAP T 2002-02-01-08.06.43.338840 INST1 VOL T 2002-02-01-08.09.31.993480 INST1 CLAS_FSC T 2002-02-01-05.58.37.116281 INST1 DCLM T 2002-02-01-06.18.12.201697 INST1 IMAG T 2002-02-01-07.12.23.437532 INST1 LREP T 2002-02-01-07.15.05.067614 INST1 MAIN T 2002-02-07-23.37.23.661084 INST1 MESG T 2002-02-01-08.04.32.310883 INST1 OABS T 2002-02-01-08.04.32.446179 INST1 PARN T 2002-02-01-08.05.38.331181 INST1 UPC_TTL T 2002-02-01-08.07.12.621892 INST1 ABST T 2002-02-07-22.14.10.217590 INST1 ASSG T 2002-02-07-22.15.53.901159 INST1 DESIGNATED T 2002-02-07-22.44.41.740703 INST1 ERRATA T 2002-02-01-07.10.21.003333 INST1 FAMI T 2002-02-01-07.10.21.316493 INST1 FREF T 2002-02-01-07.10.52.214684 INST1 PRIR T 2002-02-07-23.40.31.563919 INST1 REPIMAG T 2002-02-01-08.06.34.728666 INST1 ASSN T 2002-02-01-05.39.08.405711 INST1 CLAS_ICL T 2002-02-07-22.22.21.069014 INST1 CLAS_XCL T 2002-02-07-22.34.26.341812 INST1 DETD T 2002-02-01-07.07.41.619207 INST1 INVT T 2002-02-07-22.49.14.478119 INST1 ORDERSCREEN T 2002-02-01-08.04.32.597307 INST1 OREF T 2002-02-01-08.05.27.432029 INST1 PRIR_ORGINA T 2002-02-01-08.06.34.430435 INST1 TITL T 2002-02-01-08.07.12.432836 INST1 UREF T 2002-02-01-08.09.31.788796 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To identify and resolve a DB2 bottleneck, try these commands db2 "list applications" db2 "list applications show detail" Useful to get the application handle, which you can use to identify your query in a db2 "get snapshot for applications on patent" If you just want to see how long DB2 thinks a long query is going to take, i.e. you want the "SQL compiler cost estimate in timerons" row from the snapshot command, if you know a unique string in your select, you can db2 "get snapshot for applications on patent" | grep -p 'unique string' | grep timerons ********************************************************************** * To give you a feel for timeron units, * * * * Date Machine timerons Elapsed Time Seconds/Million * * 6-12-2002 dephds059 228,144,960 99 seconds 0.434 * * 8-21-2002 ips03i 4,470,782 Never finished ??? * * 8-27-2002 dephds059 1,970,413 614 seconds 311.675 * * * ********************************************************************* db2 "get snapshot for locks on patent" If you see a lot of "Application status = Lock-wait", then look at the corresponding "ID of agent holding lock" line to see which process it's queued behind. So something like this may be useful, db2 "get snapshot for locks on patent" | "ID of agent holding lock" or db2 "get snapshot for locks on patent" | "ID of agent holding lock" | sort -u To identify problems, see also the diaglog at /home/inst1/sqllib/db2dump/db2diag.log To kill a process, db2 "force applications (487)" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - If you can't find your application in the db2 snapshot (like I couldn't do on 11/18/2003 'cause I had control-c'd out of a for i in 03 04 05 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 do db2 connect to patent>/dev/null echo "Doing usp0$i ... at $(date)" db2 -f usp0$i/UpdateDB2|egrep -v 'completed successfully|^$'>usp0$i/UpdateDB2.61.out done and didn't notice it was still hung up in DB2 'till I tried to log out. I got that "You have running jobs" msgs when I tried to exit. The problem I had was identifying which db2 connection was mine. My UpdateDB2 list of db2 commands, had update & insert commands in it, but that string didn't appear in any "Dynamic SQL statement text" statements of the snapshot. It turned out I must have killed it when it was doing a commit, so I couldn't find myself by looking for the string. Carol finally found me by looking at the timestamp of my PID, i.e. dephds061[/dfs/cdrom]$ ps -ef|grep pts/8 inst1 28030 250432 0 11:53:24 pts/8 0:04 db2 -f usp003/UpdateDB2 inst1 42578 250432 6 19:56:56 pts/8 0:00 ps -ef inst1 161124 250432 1 19:56:56 pts/8 0:00 grep pts/8 inst1 250432 207950 0 11:52:32 pts/8 0:00 -ksh PID 28030 wasn't in the snapshot, but you can see it started at 11:53:24. Also, there was another clue. The db2bp daemon is what's used when you do a db2 connect, and there was one associated with my ksh PID (250432). inst1 253088 1 0 11:53:22 - 15:13 /home/db2inst1/sqllib/bin/db2bp 250432C628 5 Notice the PID at the end, with a constant "C628 5" tacked on. That was me. And THAT process ID (253088) WAS in the db2 snapshot as application 625, so that was the one to db2 "force applications (625)" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I wanted to see how many images we are missing and for which weeks for US Applications in Japan. Here are the queries I came up with. db2 "select distinct isd from main where patn like 'US22%'" showed that there were 8 different ISD's, ISD ---------- 01/03/2002 01/10/2002 01/17/2002 01/24/2002 01/31/2002 02/07/2002 02/14/2002 02/28/2002 or better yet, to get the main counts for each ISD, db2 "select isd,count(*) as count from main where patn like 'US22%' group by isd" yielding ISD COUNT ---------- ----------- 01/03/2002 2728 01/10/2002 2207 01/17/2002 2540 01/24/2002 3437 01/31/2002 2999 02/07/2002 3018 02/14/2002 3015 02/28/2002 3374 I could then select each ISD in this query db2 "select count(*) from imag where patn in (select patn from main where patn like 'US22%' and isd='01/03/2002')" which returns 1377, which says there are 1377 imag entries for that week, so we're missing 2728-1377 = 1351 images. But how do I combine these? Our (Carol & I) first attempt was a 3-level select db2 "select isd,count(i.patn) from main m,imag i where i.patn=m.patn and i.patn in (select patn from main where isd in (select distinct isd from main where patn like 'US22%')) group by isd order by isd" This indeed was a thing of beauty which we think would have worked, but it never finished. Then Carol turned the query around, focusing on counting main:patn's with an additional "exists" in imag clause. db2 "select isd,count(m.patn) as count from main m where patn like 'US22%' and \ exists (select i.patn from imag i where i.patn=m.patn) \ group by isd order by isd" (or for Southbury, db2 "select isd,count(m.patn) as count from main m where patn like 'US22%' and \ datasrc='UPA' and \ exists (select i.patn from imag i where i.patn=m.patn) \ group by isd order by isd" ) which returned how many images we DID have. ISD COUNT ---------- ----------- 01/03/2002 1377 01/10/2002 2207 01/17/2002 2538 01/24/2002 3436 01/31/2002 2998 02/07/2002 3009 To see the opposite, how many images we are missing, negate the "exists", db2 "select isd,count(m.patn) as count from main m where patn like 'US22%' and \ not exists (select i.patn from imag i where i.patn=m.patn) \ group by isd order by isd" which returned how many images we were missing, ISD COUNT ---------- ----------- 01/03/2002 1351 01/17/2002 2 01/24/2002 1 01/31/2002 1 02/07/2002 9 02/14/2002 3015 02/28/2002 3374 To select the exact patent numbers for the missing images, remove the count() function and limit it to the weeks with the smaller number of missing images, ala db2 "select isd,m.patn from main m where patn like 'US22%' and m.isd>'01/03/2002' and m.isd<'02/14/2002' and not exists (select i.patn from imag i where i.patn=m.patn) order by isd,patn" ISD PATN ---------- ------------ 01/17/2002 US22007503A1 01/17/2002 US22007504A1 01/24/2002 US22010953A1 01/31/2002 US22012102A1 02/07/2002 US22016213A1 02/07/2002 US22015280A1 02/07/2002 US22015889A1 02/07/2002 US22014179A1 02/07/2002 US22014254A1 02/07/2002 US22014362A1 02/07/2002 US22014458A1 02/07/2002 US22016701A1 02/07/2002 US22016420A1 On 3-14-2002, here were the 2001 "missing" US Application images, db2 "select isd,count(m.patn) as count from main m where patn like 'US21%' and not exists (select i.patn from imag i where i.patn=m.patn) group by isd order by isd" ISD COUNT ---------- ----------- 06/07/2001 1 12/13/2001 1 For the specific patent numbers, db2 "select isd,m.patn from main m where patn like 'US21%' and not exists (select i.patn from imag i where i.patn=m.patn) order by isd,patn" ISD PATN Manual Investigation Revealed ---------- ------------ ================================================== 06/07/2001 US21003204A1 Withdrawn per USPTO 12/13/2001 US21050839A1 Withdrawn per USPTO - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Notes on playing with the DB2 Utilities on my Windows 2000 Thinkpad - Could not get the Script center to correctly run a semicolon-delimited script. There is a setting within Tools/Tools Settings/General that says "Use statement termination character ;" that you can check on or off, but it appears to have no affect. Scripts must not have semicolon delimiters and you must use the continuation sequence (" \"). - See ~jasper/aixnotes/db2_examples/W2K_DB2_DDL for my script to define all the basic NPO Patent database tables, indexes, triggers, etc. - The following script was run on 5-17-2002 to collect a sample. #!/bin/ksh db2 connect to patent user inst1 using inst1_password > /dev/null for i in main abst assg clas_icl clas_xcl clas_xcl designated imag invt prir do db2 "export to $i.ld of del modified by coldel| chardel0x01 \ select * from $i where patn in \ (select patn from main where isd>='2002-05-01')" done This collected a decent sized sample from Patolis's database, 13,099 rows from abst 17,522 rows from assg 30,234 rows from clas_icl 23,095 rows from clas_xcl 298,557 rows from designated 14,021 rows from imag 39,594 rows from invt 16,261 rows from main 14,797 rows from prir 467,180 rows total - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - What's the difference between a table having a Primary Key versus a Unique Index? In Southbury, the imag table has a Primary Key of patn, datasrc, cdlabel. In Patolis, their imag table has a Unique Index of patn, cdlabel. I had a 66,177-line load file I needed to install at Patolis, that fixed some bad 2002 imag data. 64,239 lines were updates, the other 1,938 lines were new data. It was the mixture of udpates and new data, along with the Unique Index that killed me. I first tried this command, db2 "import from raj.junk.3 of del modified by coldel| chardel0x01 commitcount 1000 \ insert into inst1.imag (PATN, SRH, ABS, DESC, AMEND, DRAWING, CLAIM, \ BIBLIO, IMAGE_PAGES, BIB_STATUS, CDLABEL, XKIND)" but got this message for all the update rows, SQL3148W A row from the input file was not inserted into the table. SQLCODE "-803" was returned. SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "5" constrains table "INST1.IMAG" from having duplicate rows for those columns. SQLSTATE=23505 Evidently, db2 import/insert doesn't update existing rows, it just tries to insert them and if the row already exists, you get this Unique Index constraint violation. I then tried this command, replacing the insert with insert_update, db2 "import from raj.junk.3 of del modified by coldel| chardel0x01 commitcount 1000 \ insert_update into inst1.imag (PATN, SRH, ABS, DESC, AMEND, DRAWING, CLAIM, \ BIBLIO, IMAGE_PAGES, BIB_STATUS, CDLABEL, XKIND)" and got SQL3203N The INSERT_UPDATE option is not permitted for the specified target because it has no primary key or all columns are in the primary key. The insert_update mode of the import utility requires the target table to have a primary key. Southbury's imag table DOES have a primary key, so this isn't a problem in Southbury, but Patolis's imag table does NOT have a primary key. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To create the Japanese Conjuction table (JACONJ), db2 "create table jaconj (pno10 char(10) not null, \ pta char(1), \ apn char(31), \ jakind char(1), \ jano char(10))" db2 "grant alter,delete,index,insert,select,references,update on jaconj to ipsadmin" db2 "grant select on jaconj to ipsrun" db2 "grant select on jaconj to group www" Run my jaconj.pl script to create the japconv.ld load file, and to load, db2 "import from jaconj.ld of del modified by coldel| chardel0x01 \ commitcount 1000 insert into inst1.jaconj" After the data is all loaded, to create the index, db2 "create unique index jaconj_pno10 on jaconj(pno10)" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And for the Japanese Title table (JATITL), db2 "create table jatitl (pno10 char(10) not null, ttl varchar(350))" db2 "grant alter,delete,index,insert,select,references,update on jatitl to ipsadmin" db2 "grant select on jatitl to ipsrun" db2 "grant select on jatitl to group www" db2 "create unique index jatitl_pno10 on jatitl(pno10)" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And for the Pre-Translated Japanese Abstracts table (JAPTA), db2 "create table japta (pno10 char(10) not null, \ num smallint, \ type char(4), \ pav varchar(3200), \ pal long varchar)" db2 "grant alter,delete,index,insert,select,references,update on japta to ipsadmin" db2 "grant select on japta to ipsrun" db2 "grant select on japta to group www" db2 "create unique index japta_pno10_num on japta(pno10,num)" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Use the -x option to the db2 command, to eliminate the headers. For example, db2 "select patn,image_pages,cdlabel from imag where patn='WO00018968A1'" returns PATN IMAGE_PAGES CDLABEL ------------ ----------- --------------- WO00018968A1 1 impact2003015 1 record(s) selected. but db2 -x "select patn,image_pages,cdlabel from imag where patn='WO00018968A1'" returns just this one line, WO00018968A1 1 impact2003015 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The db2 command is documented in the "Command Reference" (not the "SQL Reference"). -c = Automatically commit each command. -f filename = Read commands from a file, not from STDIN or the command line. -v = echo each db2 command before executing it. -t = Use semicolon as statement termination character AND disables the backslash (\) as the line continuation character. -x = Do not display "select" headers (e.g. column names). This option is not documented in the Command Reference, surprisingly enough. -z filename = Redirect all output to file. It's that -t option that makes you delimit statements with a semicolon or just a new line. With -t, you need semicolons and you can split lines like this; Without -t, you don't need semicolons and you can split lines \ like this -- A double dash like this line has, is the comment indicator. So there are 2 ways to write a DB2 script, use -t ========================================================================= = = = -- Run this script by db2 -tf raj.sql.1 = = -- Note semicolons to terminate a line and no \ to continue a line = = connect to patent user inst1 using inst1_password; = = = = select patn,datasrc,namesrc,cdlabel,image_pages = = from imag where patn='US00000001__'; = = connect reset; = = = ========================================================================= or don't use -t, ========================================================================= = = = -- Run this script by db2 -f raj.sql.2 = = -- Note no semicolons and the \ to continue a line. = = connect to patent user inst1 using inst1_password = = = = select patn,datasrc,namesrc,cdlabel,image_pages \ = = from imag where patn='US00000001__' = = connect reset = = = ========================================================================= Or if you already have a database connection active, you can leave off the connect. And of course, you can include or not, the -x and -v options to the db2 command. By the way, despite starting db2 up with -tf, the ps -ef shows otherwise. EG, db2 -tf reorg_gdesc.sql -z reorg_gdesc.out shows up in a ps -ef command as inst1 63118 61668 0 16:16:16 pts/0 0:00 db2 -f reorg_gdesc.sql -z reorg_gdesc.out note that the "t" is missing ("-f" instead of "-tf") - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - If you ever get this error message SQL30082N Attempt to establish connection failed with security reason "7" ("USERID REVOKED"). SQLSTATE=08001 when trying to connect to DB2, then the number of failed login attempts on the database machine for that user, has been exceeded. To check, log onto the database machine, and type lssec -f /etc/security/lastlog -a unsuccessful_login_count -s inst1 It will come back and say inst1 unsuccessful_login_count=20 To reset it, as root on the database machine, chsec -f /etc/security/lastlog -a unsuccessful_login_count=0 -s inst1 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ============= Duplicated from my cron.at file ====================== When running a script/job from cron, your environment is NOT the same as it is when running from the command line. In particular, your PATH is set to just /usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin, not what it's normally set to in your ~/.profile. Another difference when running a cron job that connects to a database, is you need to set the DB2INSTANCE environment variable. Here's what I had to do in my ~inst1/Japio.US.Update/inpadoc.summary.pl job. # The following two lines are only needed in a cron environment. $ENV{"PATH"} = $ENV{"PATH"} . ":/home/db2inst1/sqllib/bin"; $ENV{DB2INSTANCE}="inst1"; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Tom has a web page (not http://w3/~tom) that describes any DB2 database cataloged on penguin, at http://penguin/admin. Select that first link, "DB2 Table Browser". - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - What are our largest images, page-count wise? db2 "select patn,image_pages from imag where image_pages is not null order by image_pages desc fet> PATN IMAGE_PAGES ------------ ----------- EP00683233A3 14190 WO00160860A2 11750 WO04047728A2 8598 WO04030615A2 7273 WO00157190A2 6221 WO00058473A2 5509 WO04060270A2 5454 WO00192335A1 5323 WO00170979A3 4969 WO00234771A2 4525 WO00147944A2 4144 WO03025132A2 3948 WO00175067A3 3878 WO00151628A3 3699 WO00151628A2 3695 WO08910256A1 3672 US22174153A1 3354 US23043949A1 3353 US06130602__ 3335 US06198332__ 3335 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I wanted to find all patents that were NOT in TPS's raid.done and also NOT in raid.conc. Both tables have cc=Country Code & pn=Patent Number. How do you find something that's NOT in a table? Something like db2 "select pn from raid.done where not exists (select pn from raid.done) and not exists (select pn from raid.conc)" doesn't work 'cause you'll always get "0 record(s) selected." Carol suggested using "WITH" to create a temporary table containing all possible pn's, and use that for your ... not exists in raid.done and not exists in raid.conc clauses. There's an example of using WITH on page 210 of the "Using the New DB2" book. This worked like a champ. This is my find.pn.db2 on marge01us. with fake(n) as (values(1000000) union all select n+1 from fake where n<1400000) select n from fake where not exists (select * from raid.done where cc='EP' and pn=n) and not exists (select * from raid.conc where cc='EP' and pn=n); Run by, db2 -tf find.pn.db2 The first 4 lines create a temporary table I'm calling fake, with one column, n. Then I'm using recursion to iterate over all values from 1,000,000 to 1,400,000. That populates the fake table with 400,001 rows. I then use that fake table as my indexes to my two "not exists" clauses. For the curious, I found 285 records in that range, that don't exist in either table. Examples range from EP01000555 to EP01398856. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To check out the Work Files saved in the ipnnc database in Southbury, logon as inst1 on dephds074 (the Net Commerce Front-End machine) db2 connect to ipnnc user inst1 using inst1_password db2 "select count(*) from worklist where wlsystem=1" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To install the DB2 Version 7.2 Client code, as root, Have 107 MB free in /usr Mount the "DB2 Version 7.2 Application Development Clients for UNIX-based systems" cd, mount /cd cd /cd ./db2setup Selected both the DB2 Administration Client and the DB2 Application Development Client Took the default of "Do not create a DB2 Instance." This installed Fileset Level Description --------------------------------------------------------------- db2_07_01.adt.rte 7.1.0.40 Application Development Tools db2_07_01.adt.samples 7.1.0.40 ADT Sample Programs db2_07_01.cj 7.1.0.40 Java Common files db2_07_01.client 7.1.0.40 Client Application Enabler db2_07_01.cnvucs 7.1.0.40 Code Page Conversion Tables - db2_07_01.conv.jp 7.1.0.40 Code Page Conversion Tables - db2_07_01.conv.kr 7.1.0.40 Code Page Conversion Tables - db2_07_01.conv.sch 7.1.0.40 Code Page Conversion Tables - db2_07_01.conv.tch 7.1.0.40 Code Page Conversion Tables - db2_07_01.jdbc 7.1.0.40 Java Support db2_07_01.ldap 7.1.0.40 DB2 LDAP Support db2_07_01.spb 7.1.0.40 Stored Procedure Builder I already have the inst1 userid defined as uid=201, group dbadmin1, gid=201 To create the DB2 client instance, /usr/lpp/db2_07_01/instance/db2icrt inst1 (If you have trouble with this command, try the debug (-d) option) (And if you're interested, the file /var/db2/v71/profiles.reg) (is where AIX/DB2 remembers who the instances are. ) (And another sidenote, to get rid of an instance, ) (type /usr/lpp/db2_07_01/instance/db2idrop inst1 ) Lastly, put this line in your /etc/services file, db2_inst1 3700/tcp To catalog the database nodes rhino, trantor, dephds059 and dephds061, su - inst1 db2 list node directory To see what they are db2 "catalog tcpip node rhino remote rhino SERVER db2_inst1 REMOTE_INSTANCE inst1" db2 "catalog tcpip node trantor remote trantor SERVER db2_inst1 REMOTE_INSTANCE inst1" db2 "catalog tcpip node dephds59 remote dephds059 SERVER db2_inst1 REMOTE_INSTANCE inst1" db2 "catalog tcpip node dephds61 remote dephds061 SERVER db2_inst1 REMOTE_INSTANCE inst1" db2 uncatalog node xxxx If you make a mistake And finally, to catalog the databases themselves, db2 list db directory To see what they are db2 catalog db patent as pdbrhino at NODE rhino AUTHENTICATION SERVER db2 catalog db patent as pdbtran at NODE trantor AUTHENTICATION SERVER db2 catalog db patent as pdb59 at NODE dephds59 AUTHENTICATION SERVER db2 catalog db patent as pdb61 at NODE dephds61 AUTHENTICATION SERVER db2 uncatalog db xxxx If you make a mistake - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - To see how the database disks are layed out, - Connect to the database as the database owner, - db2 list tablespaces show detail For any one tablespace (today I was interested in TEMPSPACE, which had a "Tablespace ID" of 1, - db2 list tablespace containers for 1 show detail which showed that on dncdb1, the TEMPSPACE was indeed spread out across the 3 containers, /db/db2data/db1fs/tmpspc1 /db/db2data/db2fs/tmpspc1 /db/db2data/db3fs/tmpspc1 but since these were on the same VG (thus PV), it was on the same disks. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - If you get this error when running the db2 command on a Windows system, DB21061E Command line environment not initialized. try running the db2cmd command, which starts up another DOS window in which you can then type db2 and do what you want. The differences are the db2cmd window has different environment variables set, e.g. DB2CLP=20555537 DB2INSTANCE=DB2 DB2PATH=C:\Program Files\IBM\SQLLIB DB2TEMPDIR=C:\PROGRA~1\IBM\SQLLIB (You can type just "set" to see all the environment variables in a DOS window) I think it's only the first environment variable that is critical. You can run the db2 command after set DB2CLP=20555537 (you can set DB2CLP to see what it's set to, or set DB2CLP= to unset/undefine it) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Steps I had to take to define the samples database on my laptop (raj) to my jasper AIX machine. 1) Get raj.delphion.com in the DNS server. My TCPIP Advanced Properties did not have the "Register this connection's addresses in DNS" box checked. I checked that, then I saw that l5cpoulos.delphion.com was registered in the DNS with my IP address. I went in to the DNS GUI and removed it. My raj.delphion.com IP name immediately registered, so now my AIX machine can find the raj IP name, which hopefully will remain accurate even if I renew my DHCP-leased, IP address. 2) As root, add this line to jasper's /etc/services. db2_raj 50000/tcp 3) Register the raj node in my db2 node directory. On jasper, db2 catalog tcpip node raj remote raj server db2_raj remote_instance jasper 4) Register the samples database. On jasper, db2 catalog db sample as sample at node raj authentication server I can now connect to the samples database on raj. From jasper on jasper, db2 connect to sample user jasper using newyear6; (the ; is part of my current password)