HPL and compressed files

Posted in 2004

Topics: Performance & Tuning, Error Codes & Troubleshooting

JHAYS2@sears.com — Tue, 23 Nov 2004 19:47:05 — source: IIUG Forums & Mailing Lists

Hello all, I am trying to load some very large files (which are compressed) using High Performance Loader. I finally got a load working using HPL and uncompressing through a pipe. It loads the first 3 tables then dies with the error below. I can't uncompress the file(s) before loading due to lack of storage. My hardware and software is: Informix Dynamic Server 2000 Version 9.21.UC4 SunOS nova 5.8 Generic_108528-08 sun4u sparc SUNW,Ultra-Enterprise Wed Nov 17 15:00:06 CST 2004 Log file is: /export/home/informix/logs/cr-ld-tables-ccnc-rep.logWed - - - - - - - - - - - - - - - - - Wed Nov 17 15:00:50 2004 15:00:50 pl_main.c, line 546, thread 11, proc id 22441, Segmentation Violation. 15:00:50 PANIC: Attempting to bring system down 2: 2: cannot open 3: 3: cannot open 3: 3: cannot open ./cr-ld-tables.sh.hpl[41]: 22431 Killed 3: 3: cannot open 2: 2: cannot open 2: 2: cannot open 2: 2: cannot open 2: 2: cannot open 3: 3: cannot open Broken Pipe - - - - - - - - - - - - - - - These are the compressed files I am trying to load. -rw-r--r-- 1 informix informix 444 Nov 18 16:16 ps-note.unl.Z -rw-r--r-- 1 informix informix 959 Nov 18 16:16 ps-location=5Ftbl.unl.Z -rw-r--r-- 1 informix informix 42926 Nov 18 16:16 ps-rb-wf-msg.unl.Z -rw-r--r-- 1 informix informix 156838 Nov 18 16:16 ps-rc-case.unl.Z -rw-r--r-- 1 informix informix 89901 Nov 18 16:16 ps-rc-case-note.unl.Z -rw-r--r-- 1 informix informix 3 Nov 18 16:16 ps-rc-resolution.unl.Z -rw-r--r-- 1 informix informix 390 Nov 18 16:16 ps-rd-site.unl.Z -rw-r--r-- 1 informix informix 1121 Nov 18 16:16 ps-rf-grp-member.unl.Z -rw-r--r-- 1 informix informix 661 Nov 18 16:16 ps-rf-provider-grp.unl.Z -rw-r--r-- 1 informix informix 1219 Nov 18 16:16 ps-sr-msg-area.unl.Z I can load the first two, but it fails on the third sometimes. I have removed the third from the list and it fails on what is 4th above. Same for the 5th. However, if I remove the first 5 files above, it will load the next 5. So, bottom line, I can load files 1, 2, and sometimes 3, and also 6, 7, 8, 9, 10, which are the smaller files. However, these are test files. The real files are all much larger. - - - - - - - - - - - - - - - Here is a good output log: Wed Nov 17 15:00:39 2004 SHMBASE 0xa000000CLIENTNUM 0x49010000 Session ID 91 Load Database -> ccnc-rep Load Table -> ps-rb-wf-msg Device Array -> ps-rb-wf-msg Record Mapping -> ps-rb-wf-msg Convert Reject -> /tmp/ps-rb-wf-msg.rej Database Load Completed -- Processed 406 Records Records Inserted-> 406 Detected Errors--> 0 Engine Rejected--> 0 Wed Nov 17 15:00:41 2004 - - - - - - - - - - - - - - Here is the bad output log: Wed Nov 17 15:00:48 2004 SHMBASE 0xa000000CLIENTNUM 0x49010000 Session ID 93 Load Database -> ccnc-rep Load Table -> ps-rc-case Device Array -> ps-rc-case Record Mapping -> ps-rc-case Convert Reject -> /tmp/ps-rc-case.rej - - - - - - - - - - - - - - - - - - My device in ipload is defined as: PIPE zcat <compressed file name> (The above "<>" are not redirection symbols) The logic is: Create array of table names Delete all 10 tables Create all 10 tables Start loop Create piped file Call onpload as: onpload -p ccnc-rep -j ${TBL[$I]} -flc -i $COMMITCT -l ~/scripts/ccnc=5Frep/logs/${TBL[$I]}.logRemove piped file End loop Create indexes I have varied the commit count from 500 down to 1 in steps, but it still fails on the same files. Does anyone have a suggestion other than upgrading IDS (which is being seriously considered at this time)? Thanks all.

↪ replying to JHAYS2@sears.com

MARION SUSSUJEW — Wed, 24 Nov 2004 20:53:13 — source: IIUG Forums & Mailing Lists

Hi, if your table contains text or byte field you should check if the files are terminated correctly. It should contain a delimiter behind the last field or a newline at the end. You should unpack the files and check the last lines. I saw exactly the same error message when loading such files. Also be sure not to run onpload in the background and remove the pipe file to early. If you don't have an idea you can switch on tracing: onpload -j <job> -flcM -L 5 ... You will get some more messages in the logfile. Good luck.

↪ replying to JHAYS2@sears.com

Pravin Kedia — Tue, 30 Nov 2004 23:54:12 — source: IIUG Forums & Mailing Lists

--0__=08BBE5CFDF11E26E8f9e8a93df938690918c08BBE5CFDF11E26E Content-type: multipart/alternative; Boundary="1__=08BBE5CFDF11E26E8f9e8a93df938690918c08BBE5CFDF11E26E" --1__=08BBE5CFDF11E26E8f9e8a93df938690918c08BBE5CFDF11E26E Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Hi Jhays, I think it is the proble with the data that your are trying to insert. It is failing on files 3, 4 and 5. So can you try loading these files one by one using onpload and see if = it fails sometimes/always. Try loading these test files almost 5 times. Also try adding -L 5 option at the end of the onpload command and pleas= e send me the log files. Also do you have any access to latest 9.40 release. Can you please try loading these files using that version. 9.21 version is quite old. Also can you please contact our tech support people so that they open a= case that will help me in working more closely with this case. Regards, Pravin. = JHAYS2@sears.com = Sent by: = forum.subscriber@ = To iiug.org ids@iiug.org = = cc = 11/23/2004 01:47 Subj= ect PM HPL and compressed files [3764] = = = = = = = Hello all, I am trying to load some very large files (which are compressed) using High Performance Loader. I finally got a load working using HPL and uncompressing through a pipe. It loads the first 3 tables then dies wi= th the error below. I can't uncompress the file(s) before loading due to l= ack of storage. My hardware and software is: Informix Dynamic Server 2000 Version 9.21.UC4 SunOS nova 5.8 Generic_108528-08 sun4u sparc SUNW,Ultra-Enterprise Wed Nov 17 15:00:06 CST 2004 Log file is: /export/home/informix/logs/cr-ld-tables-ccnc-rep.logWed - - - - - - - - - - - - - - - - - Wed Nov 17 15:00:50 2004 15:00:50 pl_main.c, line 546, thread 11, proc id 22441, Segmentation Violation. 15:00:50 PANIC: Attempting to bring system down 2: 2: cannot open 3: 3: cannot open 3: 3: cannot open ./cr-ld-tables.sh.hpl[41]: 22431 Killed 3: 3: cannot open 2: 2: cannot open 2: 2: cannot open 2: 2: cannot open 2: 2: cannot open 3: 3: cannot open Broken Pipe - - - - - - - - - - - - - - - These are the compressed files I am trying to load. -rw-r--r-- 1 informix informix 444 Nov 18 16:16 ps-note.unl.Z -rw-r--r-- 1 informix informix 959 Nov 18 16:16 ps-location=3D5Ftbl.unl.Z -rw-r--r-- 1 informix informix 42926 Nov 18 16:16 ps-rb-wf-msg.unl.= Z -rw-r--r-- 1 informix informix 156838 Nov 18 16:16 ps-rc-case.unl.Z -rw-r--r-- 1 informix informix 89901 Nov 18 16:16 ps-rc-case-note.unl.Z -rw-r--r-- 1 informix informix 3 Nov 18 16:16 ps-rc-resolution.unl.Z -rw-r--r-- 1 informix informix 390 Nov 18 16:16 ps-rd-site.unl.Z -rw-r--r-- 1 informix informix 1121 Nov 18 16:16 ps-rf-grp-member.unl.Z -rw-r--r-- 1 informix informix 661 Nov 18 16:16 ps-rf-provider-grp.unl.Z -rw-r--r-- 1 informix informix 1219 Nov 18 16:16 ps-sr-msg-area.un= l.Z I can load the first two, but it fails on the third sometimes. I have removed the third from the list and it fails on what is 4th above. Sam= e for the 5th. However, if I remove the first 5 files above, it will loa= d the next 5. So, bottom line, I can load files 1, 2, and sometimes 3, a= nd also 6, 7, 8, 9, 10, which are the smaller files. However, these are t= est files. The real files are all much larger. - - - - - - - - - - - - - - - Here is a good output log: Wed Nov 17 15:00:39 2004 SHMBASE 0xa000000CLIENTNUM 0x49010000 Session ID 91 Load Database -> ccnc-rep Load Table -> ps-rb-wf-msg Device Array -> ps-rb-wf-msg Record Mapping -> ps-rb-wf-msg Convert Reject -> /tmp/ps-rb-wf-msg.rej Database Load Completed -- Processed 406 Records Records Inserted-> 406 Detected Errors--> 0 Engine Rejected--> 0 Wed Nov 17 15:00:41 2004 - - - - - - - - - - - - - - Here is the bad output log: Wed Nov 17 15:00:48 2004 SHMBASE 0xa000000CLIENTNUM 0x49010000 Session ID 93 Load Database -> ccnc-rep Load Table -> ps-rc-case Device Array -> ps-rc-case Record Mapping -> ps-rc-case Convert Reject -> /tmp/ps-rc-case.rej - - - - - - - - - - - - - - - - - - My device in ipload is defined as: PIPE zcat <compressed file name> (The above "<>" are not redirection symbols) The logic is: Create array of table names Delete all 10 tables Create all 10 tables Start loop Create piped file Call onpload as: onpload -p ccnc-rep -j ${TBL[$I]} -flc -i $COMMITCT -l ~/scripts/ccnc=3D5Frep/logs/${TBL[$I]}.logRemove piped file End loop Create indexes I have varied the commit count from 500 down to 1 in steps, but it stil= l fails on the same files. Does anyone have a suggestion other than upgrading IDS (which is being seriously considered at this time)? Thanks all. = --1__=08BBE5CFDF11E26E8f9e8a93df938690918c08BBE5CFDF11E26E Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> Hi Jhays, I think it is the proble with the data that your are trying to insert.<= br> It is failing on files 3, 4 and 5. So can you try loading these files one by one using onpload and see if = it fails sometimes/always. Try loading these test files almost 5 times. Also try adding -L 5 option at the end of the onpload command and pleas= e send me the log files. Also do you have any access to latest 9.40 release. Can you please try = loading these files using that version. 9.21 version is quite old. Also can you please contact our tech support people so that they open a= case that will help me in working more closely with this case. Regards, Pravin. <img src=3D"cid:10__=3D08BBE5CFDF11E26E8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for JHAYS2@sears.com">J= HAYS2@sears.com <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D08BBE5C= FDF11E26E8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul>JHAYS2@sears.com </= font> Sent by: forum.subscriber@iiug.org 11/23/2004 01:47 PM</ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D08BBE5CFDF11E26E8f9e8a93df938@us.ibm.com@

Glossary

IDS (Informix Dynamic Server)