Onpladm create job.
Marino asked why `onpladm create job` takes ~10 seconds per table on IDS 9.40 (Tru64), which becomes unworkable when regenerating jobs for many databases (up to 24 hours). Replies suggested part of the cost is one-off (creating the onpload database, compiling stored procedures) and that each command opens a connection and does transactional work, so normal instance tuning of the onpload database helps; onstat -g sql can show what runs. Richard Kofler reported a workaround: generate the job rows with a Perl script into unload files and bulk-load them into the onpload database with HPL, cutting 100 jobs from 600s to 110s. Marino asked for details on that method and no further explanation is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
Compaq Tru64 UNIX V5.1B (Rev. 2650)
IBM Informix Dynamic Server Version 9.40.FC2X1
I have a simple question:
the creation of the jobs takes about 10 seconds for table.
The command is the following on the script:
onpladm create job job_$TABNAME -p prj_$DBNAME -d
${DIRECTORY}/${TABNAME}.unl -D $DBNAME -t $TABNAME -fu -z D -T
$INFORMIXSERVER -S $INFORMIXSERVER
Is this normal or there is a way to execute this command in a faster mode?
Ciao.
Marino
On Mon, 1 Dec 2003 10:17:40 +0100, "Marino Decaro"
<dmvposta@hotmail.com> wrote:
>Hi all,
>
>
>Compaq Tru64 UNIX V5.1B (Rev. 2650)
>IBM Informix Dynamic Server Version 9.40.FC2X1
>
>I have a simple question:
>
>the creation of the jobs takes about 10 seconds for table.
>The command is the following on the script:
>
> onpladm create job job_$TABNAME -p prj_$DBNAME -d
>${DIRECTORY}/${TABNAME}.unl -D $DBNAME -t $TABNAME -fu -z D -T
>$INFORMIXSERVER -S $INFORMIXSERVER
>
>Is this normal or there is a way to execute this command in a faster mode?
>
>Ciao.
>Marino
>
>
Does this happen only the first time it is executed?
Marino Decaro asked if 10 to create a job is normal or there is a way
to execute this command in a faster mode?
It just so happens I have been playing with this, a lot, lately.
Others have mentioned the need to create the onpload database the
first time a create command is given. Hopefully, that is all you are
seeing.
However, onpladm commands cause stored procedures to execute. Some of
these have to be compiled to run the first time. That can also take
some time. I cannot tell you everything that occurs, but onstat -g
sql <sessid> can be quite helpful to figure it out.
What I can tell you, though, is that normal performance tuning rules
do apply. If your instance is not tuned well, you will see
performance issues. Each onpladm command requires a connection be
made to the instance. Then, there is a certain amount of
transactional activity which must occur. So, if the instance is slow
(for example, if more memory has to be allocated, or if memory is
being swapped, which happens frequently on my itty-bitty test server),
then your command will take longer.
In my testing, ten seconds is closer to the max than the norm, barring
exceptions like long checkpoints, which I get occasionally.
My advice is to do everything you can to tune the instance with the
onpload database.
Sincerely,
Christopher Coleman
Database Analyst
Pharmacy Division
Mediware Information Systems, Inc.
We want to create the jobs for example every week, because the database can vary during the week. We have many databases so the creation can take about 24 hours! The only possibility is the creation of multiple dbspaces (for parallelism) or the creation of the job only for the modified tables, but I don't know if I can retrieve this information(modification date) from the system catalogs. Ciao. Marino
Marino Decaro wrote:
>
> Hi all,
>
> Compaq Tru64 UNIX V5.1B (Rev. 2650)
> IBM Informix Dynamic Server Version 9.40.FC2X1
>
> I have a simple question:
>
> the creation of the jobs takes about 10 seconds for table.
> The command is the following on the script:
>
> onpladm create job job_$TABNAME -p prj_$DBNAME -d
> ${DIRECTORY}/${TABNAME}.unl -D $DBNAME -t $TABNAME -fu -z D -T
> $INFORMIXSERVER -S $INFORMIXSERVER
>
> Is this normal or there is a way to execute this command in a faster mode?
>
> Ciao.
> Marino
Here it is more like 6 seconds per created job.
Because I had to test with approx 100 tables, I did it this way:
A Perl script created all the unload files.
HPL loads this into an empty $DBNPLOAD database.
Result: duration is now 110 instead of 600 seconds per 100 load jobs
(devices do not change here, though, so this is only jobs!)
As always: YMMV
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
"Richard Kofler" <richard.kofler@chello.at> ha scritto nel messaggio news:3FCD15C1.65F24D38@chello.at... > A Perl script created all the unload files. > HPL loads this into an empty $DBNPLOAD database. > What do you mean with 'HPL loads this into an empty database'? How this can be achieved? Ciao. Marino