Error in HPL while loading data

Hello All, I am performing HPL from 11.70 instance to 12.10 instance on same Linux machine. Instances: source: ids_test1 (11.70.FC8W1) target: ids_test2 (12.10.FC6X5) I am performing HPL using three scripts as per Technote (http://www-01.ibm.com/support/docview.wss?uid=swg21587169) step1: Create job step2: unloading data step3: loading data I am able to perform below activity on source and target instance successfully except step 3 (loading data) on target with few tables: At source instance: step1 (create job) step2 (unloading data) At target instance: step 1 (create job) step 3 (loading data) For few tables it is loading data correctly, whereas for others failing below table.log error: $more test_table.log Fri Jun 17 13:38:11 2016 SHMBASE 0x0000004000000000CLIENTNUM 0x0000000049020000 Session ID 4506 Load Database -> test_db Load Table -> test_table Device Array -> test_table Record Mapping -> test_table Convert Reject -> /tmp/test_table.rej Filter Reject -> /tmp/test_table.flt Set mode of index test_table_idx1 to disabled Set mode of index test_table_idx2 to disabled Error occured at HPL failpoint: 10561 Fatal error getting stream buffer from server and online.log: 08:44:22 Assert Failed: No Exception Handler 08:44:22 IBM Informix Dynamic Server Version 12.10.FC6X5 08:44:22 Who: Session(4597, informix@vbrdbs28.ux.corp.local, 28162, 0x462dfe98) Thread(5704, stream_2.0, 462b3928, 11) File: mtex.c Line: 508 08:44:22 Results: Exception Caught. Type: MT_EX_OS, Context: mem 08:44:22 Action: Please notify IBM Informix Techical Support. 08:44:22 See Also: /usr/informix12/tmp/af.1a30f0a5 08:44:24 Thread ID 4506 will now be suspended. As per error (HPL failpoint), executed the script for below environment variables but did not worked for pending tables: export PLOAD_SHMBASE=0x000004000000000 export IFX_XFER_SHMBASE=0x000005000000000 Referred the Technote: http://www-01.ibm.com/support/docview.wss?uid=swg21683918 As per Technote, I am using same value mentioned in it.. not sure do I have to adjust based on error logs SHMBASE and CLIENTNUM values. Any assistance will be appreciated. Thank you. ~ Pravin Bankar

Hi Pravin, as it looks some server side assertions are causing these failures. Would = this reproduce for the same set of tables? If possible, please open a PMR, including HPL job definitions, table=20 schemas and af files (as mentioned in online.log). As an alternative to HPL you might also want to look into external tables=20 which also can be used for super-fast unload and load and can even use=20 pipes for simultaneous unload-load without the need for any intermediate=20 file system storage. http://www.ibm.com/support/knowledgecenter/search/external%20table?scope=3D= SSGU8G HTH, Andreas From: "PRAVIN BANKAR" <pravinebankar@gmail.com> To: ids@iiug.org Date: 18.06.2016 11:31 Subject: Error in HPL while loading data [37278] Sent by: ids-bounces@iiug.org Hello All,=20 I am performing HPL from 11.70 instance to 12.10 instance on same Linux=20 machine.=20 Instances:=20 source: ids=5Ftest1 (11.70.FC8W1)=20 target: ids=5Ftest2 (12.10.FC6X5)=20 I am performing HPL using three scripts as per Technote=20 (http://www-01.ibm.com/support/docview.wss?uid=3Dswg21587169)=20 step1: Create job=20 step2: unloading data=20 step3: loading data=20 I am able to perform below activity on source and target instance=20 successfully=20 except step 3 (loading data) on target with few tables:=20 At source instance:=20 step1 (create job)=20 step2 (unloading data)=20 At target instance:=20 step 1 (create job)=20 step 3 (loading data)=20 For few tables it is loading data correctly, whereas for others failing=20 below=20 table.log error:=20 $more test=5Ftable.log=20 Fri Jun 17 13:38:11 2016=20 SHMBASE 0x0000004000000000=20CLIENTNUM 0x0000000049020000=20 Session ID 4506=20 Load Database -> test=5Fdb=20 Load Table -> test=5Ftable=20 Device Array -> test=5Ftable=20 Record Mapping -> test=5Ftable=20 Convert Reject -> /tmp/test=5Ftable.rej=20 Filter Reject -> /tmp/test=5Ftable.flt=20 Set mode of index test=5Ftable=5Fidx1 to disabled=20 Set mode of index test=5Ftable=5Fidx2 to disabled=20 Error occured at HPL failpoint: 10561=20 Fatal error getting stream buffer from server=20 and online.log:=20 08:44:22 Assert Failed: No Exception Handler=20 08:44:22 IBM Informix Dynamic Server Version 12.10.FC6X5=20 08:44:22 Who: Session(4597, informix@vbrdbs28.ux.corp.local, 28162,=20 0x462dfe98)=20 Thread(5704, stream=5F2.0, 462b3928, 11)=20 File: mtex.c Line: 508=20 08:44:22 Results: Exception Caught. Type: MT=5FEX=5FOS, Context: mem=20 08:44:22 Action: Please notify IBM Informix Techical Support.=20 08:44:22 See Also: /usr/informix12/tmp/af.1a30f0a5=20 08:44:24 Thread ID 4506 will now be suspended.=20 As per error (HPL failpoint), executed the script for below environment=20 variables but did not worked for pending tables:=20 export PLOAD=5FSHMBASE=3D0x000004000000000=20 export IFX=5FXFER=5FSHMBASE=3D0x000005000000000=20 Referred the Technote:=20 http://www-01.ibm.com/support/docview.wss?uid=3Dswg21683918=20 As per Technote, I am using same value mentioned in it.. not sure do I=20 have to=20 adjust based on error logs SHMBASE and CLIENTNUM values.=20 Any assistance will be appreciated.=20 Thank you.=20 ~ Pravin Bankar=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Thank you Andreas for response and alternate. Yes, the failure reproduces for the same set of tables. I will check with my supervisor/manager for opening a PMR and confirm on same. Also, I did not try the external tables for unload & load data. I will test on same and let you know. Meanwhile, when I tried HPL (Export Data & Import Data) using Server Studio for failed tables in above method, it ran successful. The only pain in performing HPL using Server Studio - Need to specify the file path for each table in both places (Export Data & Import Data). I am not sure, if there is any option/method we can specify the file for 100+ tables in a database. If you can share details on this... it will be really appreciated. I will keep updated based on progress. Thanks again. Have a great time ahead. ~ Pravin Bankar

Pravin, if you have a need to unload and reload an entire database of tables using external tables, the easiest way to do that is to get my dbexport/dbimport replacement utility package myexport from the IIUG Software Repository and use that. It requires myschema (which I believe you already have), but otherwise is standalone if you use the myexport -E option to export the data using external tables. That will create a directory containing the schema, export files, and a set of scripts to import the data with external tables using myimport -E. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 20, 2016 at 7:18 AM, PRAVIN BANKAR <pravinebankar@gmail.com> wrote: > Thank you Andreas for response and alternate. > > Yes, the failure reproduces for the same set of tables. > > I will check with my supervisor/manager for opening a PMR and confirm on > same. > > Also, I did not try the external tables for unload & load data. I will > test on > same and let you know. > > Meanwhile, when I tried HPL (Export Data & Import Data) using Server Studio > for failed tables in above method, it ran successful. > > The only pain in performing HPL using Server Studio - Need to specify the > file > path for each table in both places (Export Data & Import Data). I am not > sure, > if there is any option/method we can specify the file for 100+ tables in a > database. If you can share details on this... it will be really > appreciated. > > I will keep updated based on progress. > Thanks again. Have a great time ahead. > > ~ Pravin Bankar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144b7d04652d10535b441de

Thanks a lot Art... Surely, I will give try on same & update you accordingly. Thank you. ~ Pravin Bankar

Thank you Art and Andreas for assistance in this issue.. Andreas, I tested the external table option for one of table and it went successful. However, I have to go for appropriate script which will cover all the tables for one database. If any alternate, you can share the same. Art, as discussed for myexport/myimport and related scripts. I little found complicated. But I personally go for this if no other option works for me. Thanks for sharing your utilities.. At same time I am facing issue with er_dbspace (target instance is: 12.10).. as loaded data going into er_dbspace even specifying the dbspace in schema while creating db objects. I will open new discussion for same. Thank you. ~ Pravin Bankar