 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Informix Topics / January 2004Problem loading data - 9.21.FC4
Hello all, Informix Version 9.21.FC4 OS HPUX 11.0 I am attempting to load a bunch of tables into a new database using dbaccess. I am having problems with one particular table which appears to hang. I am guessing it has something to do with the "wait (netnorm)" which is output using onstat -g (session id) The output form onstat -g ses is as follows: Informix Dynamic Server 2000 Version 9.21.FC4 -- On-Line -- Up 2 days 12:59:59 -- 4956180 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 100124 informix - 16732 sleepy 1 61440 56240 tid name rstcb flags curstk status 100764 sqlexec c0000000c4845028 Y--P--- 33056 c0000000c4845028cond wait(netnorm) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 100124 V c0000000c549c040 61440 5200 106 6 name free used name free used overhead 0 3256 scb 0 144 opentable 0 3608 filetable 0 1040 log 0 2168 temprec 0 1656 keys 0 176 ralloc 0 14728 gentcb 0 1720 ostcb 0 3416 sqscb 0 16880 sql 0 72 rdahead 0 256 hashfiletab 0 552 osenv 0 2536 sqtcb 0 3144 fragman 0 888 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 100124 INSERT arcdb CR Not Wait 0 0 9.03 Current statement name : inscur Current SQL statement : INSERT INTO tdmsma608500 values(?,?,?,?,?) Last parsed SQL statement : INSERT INTO tdmsma608500 values(?,?,?,?,?) Any ideas why it is hanging? Cheers, Howard Hi Howard. Logical log is full ? Can you post output of onstat -l ? Regards. -- Tsutomu Ogiwara from Tokyo Japan. ICQ#:168106592 >From: "Howard Jones" <howie_lfc@hotmail.com> >Reply-To: "Howard Jones" <howie_lfc@hotmail.com> [quoted text clipped - 77 lines] > >Howard _________________________________________________________________ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus sending to informix-list Hi, Try to let me know what kind of program you are using to do it !! The plataform and IDS version could help ...... I couldn't understand if you are reading from one database, and inserting in the table of another database.... in the same instance ?? Keep in touch ..... best regards RFo > Hello all, > [quoted text clipped - 70 lines] > > Howard Platform - Informix Version 9.21.FC4 Operating System - HPUX 11.0 There is a premium on space for the table unloads so I am compressing them. The script I am using is below this works on many tables both smaller and larger than the "problem" table. What I am doing is unloading all the tables within a dbspace editing the dbschemas to reference the new database/dbspace location dropping the excisting table then recreating and loading it into the new database ( which is within the same instance ). Unload script..... #!/bin/sh TEMPUNL=/tsadm/informix/reorg/archive/squeeze TEMPDIR=/tsadm/informix/reorg/archive LOGFILE=$TEMPDIR/logs/unload.sh_`date +%T`.log DEFRAGLIST=$TEMPDIR/defrag_list DB=baan INFORMIXDIR=/home/informix UNLOADDIR=$TEMPUNL/unloads [ -f $TEMPDIR/Finished ] && rm $TEMPDIR/Finished sleep 5 clear cat /dev/null > $LOGFILE date echo `date` >> $LOGFILE while read TABNAME do echo "Unloading ${DB}:${TABNAME}; \c" >>${LOGFILE} echo "Unloading ${TABNAME}; \c" dd if=${TEMPUNL} | compress | dd of=/var/adm/crash/reorg/archive/unloads/${TABNAME}.unl.Z & sleep 2 ${INFORMIXDIR}/bin/dbaccess ${DB} << EOF 2>>$LOGFILE UNLOAD TO ${TEMPUNL} SELECT * FROM ${TABNAME} ; EOF echo "DONE" >>${LOGFILE} echo "DONE" date echo `date` >> $LOGFILE done < ${DEFRAGLIST} touch Finished echo " " Load script..... #!/bin/ksh # TEMPUNL=/tsadm/informix/reorg/archive/squeeze TEMPDIR=/tsadm/informix/reorg/archive LOGFILE=$TEMPDIR/logs/$0.`date +%T`.log DEFRAGLIST=$TEMPDIR/defrag_list INFORMIXDIR=/home/informix UNLOADS=/var/adm/crash/reorg/archive/unloads DB=arcdb [ -f $TEMPDIR/Finished ] && rm $TEMPDIR/Finished sleep 5 clear cat /dev/null > $LOGFILE date echo `date` >> $LOGFILE while read TABNAME do echo "Loading data for ${DB}:${TABNAME}; \c" >>${LOGFILE} echo "Loading data for ${DB}:${TABNAME}; \c" dd if=$UNLOADS/$TABNAME.unl.Z | uncompress | dd of=$TEMPUNL & sleep 3 $INFORMIXDIR/bin/dbaccess ${DB} <<EOF 2>>$LOGFILE LOAD FROM ${TEMPUNL} INSERT INTO ${TABNAME}; EOF echo "DONE" >>$LOGFILE echo "DONE" date echo `date` >> $LOGFILE done < ${DEFRAGLIST} touch Finished > Hi, > [quoted text clipped - 80 lines] > > > > Howard Howard, Did you pre-size the EXTENT SIZE and NEXT SIZE attributes of the new table? Adding extents during a load can be very expensive in terms of time. Have you tried using dbload or the sqlreload utility from Jonathan Leffler's sqlcmd package? These will break the large load into smaller transactions for you which will reduce the resource load on the server. Also the dbcopy utility from my utils2_ak package can make the copy directly table to table also with partial transactions. Utils2_ak also contains my dbschema replacement utility, myschema, which can calculate the extent sizing for you including optional space for growth (see the -a, -m, -M, -e, & -n options). (Sqlcmd and utils2_ak are available for download from the IIUG Software Repository.) Art S. Kagel > Platform - Informix Version 9.21.FC4 > Operating System - HPUX 11.0 [quoted text clipped - 9 lines] > > Unload script..... <SNIP> Art S. Kagel You still haven't said what the nature of the "problem" is. "Hang" is a bit vague. Care to expand? Andy > Platform - Informix Version 9.21.FC4 > Operating System - HPUX 11.0 > > There is a premium on space for the table unloads so I am compressing them. > The script I am using is below this works on many tables both smaller and > larger than the "problem" table. <snip> > dd if=$UNLOADS/$TABNAME.unl.Z | uncompress | dd of=$TEMPUNL & I don't quite understand this line. Well I sort of do, but what special thing is it accomplishing that uncompress -c $UNLOADS/$TABNAME.unl.Z > $TEMPUNL. Other than putting it in the background. Could this be a syncronization problem? If this table loads faster than other tables or causes a check point, could the load statement and the dd statement get crosswised with each other? Have you ever used named pipes? that way you never have a copy of the uncompressed file on the disk and Unix handles the syncronization(spelling?). I have had great sucees with them when space is limited. Here is a sample of how it works. mknod /tmp/unload.pipe p -- May be different on some machines uncompress -c $UNLOADS/$TABNAME >/tmp/unload.pipe & dbaccess <database> EOF load from /tmp/unload.pipe insert into $TABNAME ; EOF rm /tmp/unload.pipe /tmp/unload.pipe will not actually get bigger than the size of the pipe buffer which is probably adjustable but I have never done that. Mine always end up about 2K. Disclaimer: I haven't tested this answer and I haven't done it in awhile and I almost always get it sort of right which doesn't do you any good if you aren't famillar with named pipes. If you do want to do it this way and this doesn't actually work for you and you don't know how to fix it then and only then I can actually generate the proper commands for you, but I don't want to waste the time if you have no interest in this method. I think this method can be made to work with dbload and hpload, also. Also if you are cleaning up table extents you might want to make the load table raw while you are loading it. And then add the indexes and constraints after you load it. I have also heard that some people don't like named pipes, but I haven't ever had any trouble with them that I haven't caused myself. If anyone else knows some pros or cons on named pipes I wouldn't mind hearing about it. > > dd if=$UNLOADS/$TABNAME.unl.Z | uncompress | dd of=$TEMPUNL & > [quoted text clipped - 7 lines] > > Have you ever used named pipes? that way you never have a copy of the [ ... example snipped ... ] > If you do want to do it this way and this doesn't actually work for > you and you don't know how to fix it then and only then I can actually > generate the proper commands for you, but I don't want to waste the > time if you have no interest in this method. I think this method can > be made to work with dbload and hpload, also. HPL has it all builtin. Look in the HPL users guide onpladm crate job ...... -fpl -d <name_of_pipecommand> actually does meta: <name_of_pipecommand> | onpload-reading-from-stdin when executing the job. onpladm create job ..... -fl -d <name_of_fifo> works with names pipes as expected (V9.21,V9.3,V9.40.UC2) dbload works fine using named pipes and for a very long time (> 11 years) was the solution for load-/unload files > 2GB which never ever was a show stopper - at least not for me. One needs to be on UNIX or Linux, though. > Also if you are cleaning up table extents you might want to make the > load table raw while you are loading it. And then add the indexes and > constraints after you load it. Way to go, if you use a PDQ & parallel sort setup for the index rebuild. > I have also heard that some people don't like named pipes, but I > haven't ever had any trouble with them that I haven't caused myself. > If anyone else knows some pros or cons on named pipes I wouldn't mind > hearing about it. Depending of OS version there are techniques around to increase the named pipe buffer size, but I think this could only have been as far back as the BSD days, when it was a problem to keep a slow streamer tape in streaming mode - gee I'm getting old it seems dic_k  SignatureRichard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe > > > dd if=$UNLOADS/$TABNAME.unl.Z | uncompress | dd of=$TEMPUNL & > > [quoted text clipped - 21 lines] > meta: <name_of_pipecommand> | onpload-reading-from-stdin > when executing the job. Just to repeat that, due to a bug in his release of IDS, HPL unloads are very sloooowwwww. > > > > dd if=$UNLOADS/$TABNAME.unl.Z | uncompress | dd of=$TEMPUNL & > > > [quoted text clipped - 24 lines] > Just to repeat that, due to a bug in his release of IDS, HPL unloads are > very sloooowwwww. hmm maybe I am wrong, but the OP showed a load not an unload. I am not aware that the bug in question does slow down a load, but I may be wrong, or missing something. And for all folks out there having slow HPL unloads IMHO it is always worth a try to drop any constraints (drop! not only disable) and then drop all indexes on the table one wants to unload using HPL. Then try again to unlod and see what happens. It is also a *very* good idea to have no query running under grant manager control at the time when you unload...... dic_k SignatureRichard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe > hmm > [quoted text clipped - 3 lines] > I am not aware that the bug in question does slow down > a load, but I may be wrong, or missing something. No, you're correct, it only affects unloads, not loads. > Hello all, > [quoted text clipped - 70 lines] > > Howard there are better alternatives than using INSERT INTO. Look into the migration guide on the docs web pages and use dbload instead Or, better and much faster if you have more than 5 records to put into your database: use the HPL It is very well documented in the HPL users giude on the very same ifx docs web page, which you have an etry to using this link http://www.k7.com/getID.pl?id=927059375538619&x=1166&re=2 If you must or wanna stick to the INSERT INTO method, then read the adminguide and monitor your logical log activity using onstat -l. If you do *any* type of logging, you simply have a good chance to run into troubles, if you don't. Also watch out for long transaction rollbacks, which you will find in the log file. You can check where that thingies sits using onstat -m, which does not only show you some of the last lines in that file, but also tells you the path & filename, ready to cut & paste that into your view + <this-is-the-log-file-I-want-to-see-now> command line If you are on (Win)DOS od any type, I cannot help, though good luck SignatureRichard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe HPL is NOT necessarily a good suggestion in this case, as there is a bug in 9.21FC4 that makes HPL unloads abnormally slow. SignatureNeil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com > > Hello all, > > [quoted text clipped - 98 lines] > > good luck When you say "appears to hang" what exactly do you mean? What happens next? Has it actually hung or is it just doing its thing but very slowly? A couple of of-the-top-of-head theories: - Logical logs filling up (I know you've discounted this but check again), possibly causing long transaction rollback; rollbacks can be VERY slow. What are your values of LTXHWM and LTXEHWM? Is the system useable after whatever you do to get rid of the "hang"? - Getting held at the Memory Grant Manager gate - do an onstat -g mgm and see if it's getting stuck in the "Ready" queue; - It's running but just very slowly. Do an onstat -u while it appears to be hanging. What do the flags tell you it's doing? Are the reads and writes incrementing or is it genuinely stuck? What checkpoint durations does onstat -m show? Andy > Hello all, > [quoted text clipped - 70 lines] > > Howard |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.