Faster Dbimport

Hello Forum People! What is the best ONCONFIG configuration file for a dbimport a database that has a size of 60GB flat format ?. The server is an IBM x366 with a quad-core processor, 8GB of RAM, and fiber channel disks 2 Gbits / second. The engine is a Windows TD6 IDS 7.31. Of course I appreciate your attention. Gustavo Echenique PD: Sorry for my bad english.

There are no ONCONFIG parameters that will have a major impact on dbimport performance. The environment variables FET_BUF_SIZE, PDQPRIORITY, & PSORT_NPROCS can be used to improve performance somewhat. Biggest improvement would be to use my dbimport/dbexport replacement package, myexport. The myimport script in that package, when used with the -E (external table load) and -p (parallel load) options, will provide the fastest performance. If you have not yet made the dbexport dataset, then use myexport -E -m to perform the export. That will also be faster and will set things up so that myimport can create the indexes after loading the data. Then you would use: myimport -E -m -U -p for the absolute fastest import. You can download the myexport package from the IIUG Software Repository ( www.iiug.org/software) 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 Wed, Dec 24, 2014 at 7:15 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hello Forum People! > > What is the best ONCONFIG configuration file for a dbimport a database that > has a size of 60GB flat format ?. > > The server is an IBM x366 with a quad-core processor, 8GB of RAM, and fiber > channel disks 2 Gbits / second. > > The engine is a Windows TD6 IDS 7.31. > > Of course I appreciate your attention. > > Gustavo Echenique > > PD: Sorry for my bad english. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c235641134b5050af5a32d

Hi Art! I knew you'd be the first to respond. Thank you very much for your help. Do these scripts that you're talking about work on Windows? Could you write me an example to import ?. What I need is to modify the Extents and Nexts some tables in a dbexport and then import as quickly as possible.

Hola GustavoNo hay muchos parametros para mejorar la velocidad de dbimport. Ya que toda la importacion se hace en forma secuencial, de a una tabla a la vez, y cada indice a la vez. Una mejora puede ser obtenerse seteando el PSORT_NPROCS y algunos parametros mas, para hacer que los indices se creen mas rapido. Lo unico q podria cambiar muuuucho la performance es usar discos de estado solido aprovecho para comentarte que junto a otra persona (ex soporte de Informix) hemos armado una consultora de Informix llamada tbinex.com, cualquier asesoria que necesitas, siempre estamos a tus ordenes. saludosIgnacioignacio@tbinex.com From: GUSTAVO ECHENIQUE <gustavo.echenique@cemdo.com.ar> To: ids@iiug.org Sent: Wednesday, December 24, 2014 9:15 AM Subject: Faster Dbimport [34393] Hello Forum People! What is the best ONCONFIG configuration file for a dbimport a database that has a size of 60GB flat format ?. The server is an IBM x366 with a quad-core processor, 8GB of RAM, and fiber channel disks 2 Gbits / second. The engine is a Windows TD6 IDS 7.31. Of course I appreciate your attention. Gustavo Echenique PD: Sorry for my bad english. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hola Ignacio! Te había enviado un correo ayer, y me llamó la atención que no lo contestaras. Pero ahora me acordé que siempre estás vigilando los temas de este foro. Es una auténtica alegría estar en contacto con vos nuevamente. Ahora bien, aparte de PSORTNPROCS, ¿qué otros parámetros podrían modificarse y con qué valor?. No tengas dudas que te pasaré varias tareas de consultoría. Un abrazo! Gustavo

Are you doing the export/import just to reorg the database? If you are running 11.xx or 12.10 you can do it easier and faster without downtime using the API REPACK (v11.xx+) or DEFRAGMENT (v12.10) functions after altering the EXTENT SIZE and NEXT SIZE attributes of the table. No, the scripts are ksh/bash scripts so they will not run under windows, but you can run them on a Linux/UNIX client and point them at the database on windows. That will work, sort of. The data will be written on the Windows side though. 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 Wed, Dec 24, 2014 at 8:15 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > > Hi Art! > I knew you'd be the first to respond. Thank you very much for your help. > > Do these scripts that you're talking about work on Windows? > > Could you write me an example to import ?. > > What I need is to modify the Extents and Nexts some tables in a dbexport > and > then import as quickly as possible. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134babcea313f050af771d6

Thanks Art for your responses! My engine is an Informix IDS 7.31 TD6 for Windows, and the purpose is to reorganize the Extents and Nexts tables. But I encourage you to not run the scripts in a production environment. However I will test with a Linux client pointing to another server, and when safer, the production will apply. For now, I will continue with dbimport. Again, thank you very much for your help. I wish you very happy holidays.

I've been using the myexport/myimport scripts on production for over 20 years. Recently used them to export over 1200 databases from five production servers and import the same to new servers on a new platform. However, I have not used them against a Windows server remotely myself, so test away and report any problems or challenges. You know that you should upgrade that 7.31 Windows based server to v12.10 on Linux, right? Between improvements since 7.31 and newer hardware you could double performance. 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 Wed, Dec 24, 2014 at 10:16 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > > Thanks Art for your responses! > > My engine is an Informix IDS 7.31 TD6 for Windows, and the purpose is to > reorganize the Extents and Nexts tables. > > But I encourage you to not run the scripts in a production environment. > However I will test with a Linux client pointing to another server, and > when > safer, the production will apply. > > For now, I will continue with dbimport. > > Again, thank you very much for your help. > > I wish you very happy holidays. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01177631b1b9c7050af8017f

Hi Art! Again with dbimport and performance. You know, I tried to make the dbimport with some modified in the ONCONFIG parameters. The rate was noted, but there were errors that had ever happened to me. First, it gave an error in creating a table, so I opened the sql dbexport and created manually without problems. From there, I kept running all statements in SQL Editor. Then came a constraint error, I noticed the referenced table, and had no record. I checked the "unl" table, and all records were exported, which was completely disoriented. Now I'm installing a linux to run your scripts. I wanted to consult you if the connection to the remote engine is via ODBC Informix, or via JDBC. Sorry for the inconvenience, but you could send me some examples to export and import ?. A hug! Gustavo Echenique

Are you still using dbimport? Or myimport? OK, this is a long standing problem with dbimport. Dbexport creates the schema file in tabid order and processes the loads in the same order. In theory that should prevent child tables from being loaded before parent tables. However, in practice if you have ever performed an ALTER on a parent table that couldn't be processes in-place then the table will get a new tabid which will be greater than the tabid of the child tables that reference it and loading the child table rows will trigger a constraint violation. This is also why dbimport sometimes encounters a duplicate constraint name error when creating NOT NULL constraints on columns in such a table that has a different tabid in the target database than it did in the source database. My solution for myexport/myimport is based on several things: - The latest release of myexport uses a new feature of myschema to create a schema file in dependency order if you pass the -O option. In other words the CREATE statement for dependent or child tables always follow their parent table's CREATE. - The -m option to myexport writes the schema for indexes, constraints, privileges, triggers, functions and procedures, etc. to a separate schema file from the create table statements. That's so myimport can load the data into the tables without the constraints enabled when you include the -m option to myimport as well. - The -p option to myexport exports all tables in parallel to minimize the possibility of exporting unmatched child records. Dbexport handles this by locking the database. Myexport does not lock the database. - There is an option to myexport (-F) which turns on constraint filtering during the execution of myimport so that any orphaned dependent records are written to violations tables where you can manually filter them out or reload them later. None of this should be an issue, however, if the database is not being updated during the export. Examples of using dbexport/dbimport? Simplest is: dbexport -ss mydatabasename -o /path/to/the/.exp/directory/parent dbimport mydatabasename -i /path/to/the/.exp/directory/parent -l unbuffered -d default_dbspace_for_mydatabasename 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, Dec 29, 2014 at 4:28 PM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hi Art! > Again with dbimport and performance. > You know, I tried to make the dbimport with some modified in the ONCONFIG > parameters. The rate was noted, but there were errors that had ever > happened > to me. > First, it gave an error in creating a table, so I opened the sql dbexport > and > created manually without problems. From there, I kept running all > statements > in SQL Editor. Then came a constraint error, I noticed the referenced > table, > and had no record. I checked the "unl" table, and all records were > exported, > which was completely disoriented. > Now I'm installing a linux to run your scripts. > I wanted to consult you if the connection to the remote engine is via ODBC > Informix, or via JDBC. > Sorry for the inconvenience, but you could send me some examples to export > and > import ?. > > A hug! > > Gustavo Echenique > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c217d29b9e89050b630d73

Art Hello again! You do not know how much I appreciate your kindness forever to answer all questions. The problem occurred because dbimport constraint of a table did not record any record. Moreover, much that basis was not affected by any ALTER ago. So I miss most. The example that I asked you was on using your scripts (myexport), since dbexport and dbimport the use for a long time (I have exported and imported about 500 times the database production, but I never felt that not recorded records in a table). I also need to know if the server connection is made via ODBC or JDBC. I send a heartfelt hug, and again thank you very much.

OK, so, if I remember correctly, you are using Informix v7.31, so some of the more advanced features of myexport/myimport will not work. You will not be able to use the -O (dependency ordering) or -E (export/import using external tables) option or -A for myimport (use the HP Loader via the IBM onpladm utility) for high speed exports and imports. These options depend on Informix engine features that you just do not have in 7.31. For faster exports you can use the -H D or -H E option to import and -D, -C, or -S to export faster using Ravi Khrishna's myonpload utility (which you can download from the IIUG Software Repository) to manage the HP Loader and you can use -P parallel export/import to take advantage of all of the CPU cycles and IO bandwidth available. So, using these features, I would: myexport mydatabase -C -o /path/for/.exp/directory -p -m -v and myimport mydatabase -H D -m -p -d default_dbspace As far as how myimport connects to the database, it is just a shell script. Connections to the database depend on which tools it is using to accomplish the pieces of what it does. It uses some of the following depending on options: - myschema - an ESQL/C utility from my utils2_ak package which uses a native Informix SQLI connection - sqlunload - an ESQL/C utility from Jonathan Leffler's sqlcmd package which uses a native Informix SQLI connection - dbaccess - Informix native utility which also uses SQLI connections - myonpload - an ESQL/C utility from Ravi Khrishna's myonpload package which uses a native Informix SQLI connection - HPLoader - Native Informix high speed load/unload utility. It uses a low level library interface directly into the database - sqlunload - an ESQL/C utility from Jonathan Leffler's sqlcmd package which uses a native Informix SQLI connection - sqlreload - an ESQL/C utility from Jonathan Leffler's sqlcmd package which uses a native Informix SQLI connection You should compile the utils2_ak, myonpload, and sqlcmd packages using the latest Informix CSDK (v4.10.xC4) rather than your current v2.xx release of the CSDK because it has been a long time (12 years) since that CSDK version and v7.31 of the server engine were desupported and some code that is not compatible with those older CSDK release may have slipped into the code. For my packages, I do try to keep things backwards compatible, but I cannot guarantee that the new code will compile with the older compilers. Jonathan, I know, does not like to continue to support very old CSDK releases, so his code may be ever more trouble. Myonpload is an old package and has not been modified for a while, so it should be OK with old or new CSDK versions. 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, Dec 29, 2014 at 7:40 PM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Art Hello again! > You do not know how much I appreciate your kindness forever to answer all > questions. > The problem occurred because dbimport constraint of a table did not record > any > record. Moreover, much that basis was not affected by any ALTER ago. So I > miss > most. > The example that I asked you was on using your scripts (myexport), since > dbexport and dbimport the use for a long time (I have exported and imported > about 500 times the database production, but I never felt that not recorded > records in a table). > > I also need to know if the server connection is made via ODBC or JDBC. > > I send a heartfelt hug, and again thank you very much. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b34318a9db555050b664915

Hi Art! I apologize for my ongoing consultations, but I do it because I've never worked with Linux and scripts like yours. I always have done in Windows. I have set up a Ubuntu Linux Server in a virtual machine, and I configured the last Informix Client SDK, and I connect to the database remotely via DBAccess, but do not know how to run scripts from there. If I understand, in your last answer you said that one option was to use the DBAccess. Is this well ?. If the answer is yes, how do I run your scripts ?. Clarified that are as IIUG got repository. Should we compile ?, and if so, how? Again I apologize for the inconvenience, and take the opportunity to thank you for the help. Have a great 2015! A hug! Gustavo Echenique

Gustavo: The IIUG Software Repository is a library of software and scripts contributed by Informix users. It resides on the IIUG web site at: www.iiug.org/software. For managing the process, myexport & myimport use dbaccess. However, for the actual data export/import it uses one of the following: - dbaccess for export/import using external tables (-E option available only for Informix v11.50 and later). - sqlunload/sqlreload from Jonathan Leffler's sqlcmd package for using traditional UNLOAD and LOAD processing - For export/import using the HPLoader they can use either: - The Informix hpladmin utility (available first in v10.00 IB), or - Ravi Krishna's myonpload utility. For all of these methods myexport uses my dbschema replacement utility, myschema, from my utils2_ak package. All of these packages, utils2_ak, myonpload, and sqlcmd can be downloaded for free as source code from the Repository. Yes, you have to build them. However, there are build instructions included in the packages. Quick and dirty: - For utils2_ak: Just decompress the package file (utils2_ak.gz) using gunzip and execute the resulting shell archive file (utils2_ak) using the bash shell: bash ./utils2_ak. It will unpack itself and offer to let you read the README.1st and BUILDIT text files. Building utils2_ak on Linux is trivial. Just run: ./make and it will build itself. The building instructions are for making changes to the "makefile" to support other operating systems, but I maintain it on Linux so the default makefile will work out-of-the-box. If you want to edit the file Makefile and change the entry for the tag INSTALLDIR from /usr/local/bin to wherever you want to install the executables and run: make install - For sqlcmd: Decompress the sqlcmd-88.00.tgz file using gunzip, it will leave a file with the same name but with a .tar extension. Then you unpack the tar file with: tar xvf ./sqlcmd-88.00.tar. Then cd into the directory it will create and execute the "configure" script that is there. It will configure the make files for you after detecting what versions of tools and libraries you have installed. They just type "make" to build the tool. If you want to install the executables type: make install and the executables will be installed in $INFORMIXDIR/bin for you. The file INSTALL has instructions for changing this location. - For myonpload: Extract the myonpload.zip file with "unzip myonpload". There is a file myonpload.README with build instructions but it is simple (I haven't built this myself in many years). There is only one source file myonpload.ec which you should be able to compile with: gcc -o myonpload myonpload.ec and a single SQL script, which you have to execute against the HPLoader database. 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 Wed, Dec 31, 2014 at 11:21 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hi Art! > > I apologize for my ongoing consultations, but I do it because I've never > worked with Linux and scripts like yours. I always have done in Windows. > > I have set up a Ubuntu Linux Server in a virtual machine, and I configured > the > last Informix Client SDK, and I connect to the database remotely via > DBAccess, > but do not know how to run scripts from there. > > If I understand, in your last answer you said that one option was to use > the > DBAccess. Is this well ?. If the answer is yes, how do I run your scripts > ?. > > Clarified that are as IIUG got repository. Should we compile ?, and if so, > how? > > Again I apologize for the inconvenience, and take the opportunity to thank > you > for the help. > > Have a great 2015! > > A hug! > > Gustavo Echenique > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c23564ced6ef050c146968