Easy INFORMIX questions
I am very new to informix so be kind. - Do stored procs have a procompiled/cached query plan in informix? If you have any links discussing it would be very helpful. - Does informix have anything similar to DTS packages in MS SQL Server for doing optimized nightly loads of datafiles? Currently we are using some incredibily inefficient shell script or compiled external load program and it is causing major problems holding locks and taking several hours to complete (i could do this load in SQL Server in under and hour)
Stored procs can have a precompiled "plan", it gets precompiled when the "UPDATE STATISTICS FOR PROCEDURE" statement is executed. The update stats command needs to be executed when the stored proc changes, if the indexing on the tables that the procedure uses change or the data that the procedure uses changes drastically. Most DBA's probably have an update statistics schedule that probably includes the step for procedures. As for an Informix DTS tool, HPL as Jerry suggested (I'm pretty sure that it would accept a csv file if you wanted it to but I really don't know). Other than that you could optimize your shell scripts, convert them to Perl or ESQL/C or use DTS and an ODBC/OLEDB/Linked Server connection to do what you want. I would suggest against using DTS for ETL purposes with Informix if you are already experiencing performance issues. I would focus on utilizing HPL or modifying the logic in the shell scripts to make them more performant. (According to webword.com performant is a word) :-) DL Redden ----- Original Message ---- From: zackary.evans@gmail.com To: informix-list@iiug.org Sent: Thursday, June 8, 2006 10:18:52 AM Subject: Easy INFORMIX questions I am very new to informix so be kind. - Do stored procs have a procompiled/cached query plan in informix? If you have any links discussing it would be very helpful. - Does informix have anything similar to DTS packages in MS SQL Server for doing optimized nightly loads of datafiles? Currently we are using some incredibily inefficient shell script or compiled external load program and it is causing major problems holding locks and taking several hours to complete (i could do this load in SQL Server in under and hour) _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
zackary.evans@gmail.com wrote: > - Do stored procs have a procompiled/cached query plan in informix? If > you have any links discussing it would be very helpful. Stored procedures are precompiled. If you enable the relevant caches, then query plans are cached. URLs? Beyond the generic: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp Follow the links to Administering, Administrator's Reference, Configuring and Monitoring, and Configuration Parameters. Others have answered your second Q about loading. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/