Update fields of a table from a list

[Ralf Hackmann]

Hallo,

I have 2 jobs to update Tables where the user give the data in an input text or csv list.

1) Input List with one row, one field must be change, where fields are in this row.

2) Input List has more than 2 rows, one field is primery index, I must update the other for every record of the list.

To 1)
This job I solved for my own with an temp table

with a.txt
1234
4578

CREATE temp table artikel (art CHAR(21)) with no log;

LOAD FROM "a.txt" INSERT INTO artikel (art);

UPDATE fyar2sta
SET bestverf = 64
WHERE artikel IN ( SELECT * FROM artikel );

to 2)
Here I don´t Know how to solve it

b.txt
1234;123;234;1
5678;456;789;3

The first row is primery key an I must update rows 2 to 4

I think I must import b.txt in a temp table with 4 rows and then update every record with a loop in a procedure.

But I don´t know How to code this.

Can anybody help me?

Greetings

Ralf

[Ralf Hackmann]

The Database is Informix SE 7.25

[Art Kagel]

OK, I have no idea what you want to do with the b.txt input records. How
about an example of what the resulting updates SHOULD look like.

I do, however, agree that you will need a stored procedure or an external
executable (written in some host languare like ESQL/C, 4GL, Perl-DBI, etc.)
to accomplish this. You can't do it in pure SQL.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by Ralf Hackmann
The Database is Informix SE 7.25
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Jonathan Leffler]

Post by Ralf Hackmann
I have 2 jobs to update Tables where the user give the data in an input text or csv list.
1) Input List with one row, one field must be change, where fields are in this row.
2) Input List has more than 2 rows, one field is primary index, I must
update the other for every record of the list.
To 1)
This job I solved for my own with an temp table
with a.txt
1234
4578
CREATE temp table artikel (art CHAR(21)) with no log;
LOAD FROM "a.txt" INSERT INTO artikel (art);
UPDATE fyar2sta
SET bestverf = 64
WHERE artikel IN ( SELECT * FROM artikel );

So, for this edit, the file contains the list of the primary keys for the
rows that must be updated, but the file does not contain any information
about the nature of the update itself? What you've got works, so this is
not critical.

Post by Ralf Hackmann
to 2)
Here I donŽt Know how to solve it
b.txt
1234;123;234;1
5678;456;789;3
The first row is primary key and I must update rows 2 to 4

The first row? Or the first field in each row is the primary key? You
will need to expand upon the updates you would perform based on that data —
because it is not at all clear to me what your requirement is. And without
good requirements , you'll only get mediocre answers.

I think I must import b.txt in a temp table with 4 rows and then update

Post by Ralf Hackmann
every record with a loop in a procedure.

Maybe, but it is not obvious how you get the 4 rows out of the two shown.
I assume that the semi-colon is a field separator?

[Ralf Hackmann]

Sorry, I confused row with columns.
So I have 4 columns in a.txt

If I would manualy update a in the Database I make this a.sql
UPDATE table Set b=123, c=234, d=1 WHERE a=1234;
UPDATE table Set b=456, c=789, d=3 WHERE a=5678;

But in practice a.txt ha more than 1000 rows.

Is there a way to do this in a stored procedure, if I import a.txt in a temp table with 4 columns and make the Update above in a loop over each row of the temp table?

I dont know how to code this.

[Art Kagel]

If you have 11.50 or later, then this is easy.

- Define an external table for the a.txt file:
- create external table a_ext( a int, b int, c int, d int ) using
(datafiles('disk:a.txt'), format 'delimited', delimiter ';');
- Join 'table' to the external table for the update:
- update table set (b, c, d) = (select b, c, d from a_ext where
a_ext.a = table.a) where table.a in (select a from a_ext);
- Clean up:
- drop table a_ext;

Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by Ralf Hackmann
Sorry, I confused row with columns.
So I have 4 columns in a.txt
If I would manualy update a in the Database I make this a.sql
UPDATE table Set b=123, c=234, d=1 WHERE a=1234;
UPDATE table Set b=456, c=789, d=3 WHERE a=5678;
But in practice a.txt ha more than 1000 rows.
Is there a way to do this in a stored procedure, if I import a.txt in a
temp table with 4 columns and make the Update above in a loop over each row
of the temp table?
I dont know how to code this.
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Ralf Hackmann]

In my SE 7.25 I tied this to import an it works

CREATE temp table jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;

LOAD FROM "b.txt" DELIMITER ";" INSERT INTO jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);

SELECT COUNT * FROM JPV;

=>
artikel artwgr vorzugl1 wbzkrit

00005746-0200 303 509 5
00006019-0200 303 509 5
00006835-0200 303 509 5
00007610-0200 303 509 5
00015431-0200 303 509 5

In next Step I should use the query from Art S. Kagel below and I dont need a Stored Procedure

Thanks

[Ralf Hackmann]

Hello,
if I code in production system the proposal from S.Kagel I get an error message

Here my code:

CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;

LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);

UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

( artwgr and teilfam should be eq artwgr and the other fields of b.txt should be updated in an other table)

If I run zhis I get the error message:

"201: A syntax error has occured
Error in line15
Near character position 34"
Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr

Where is my mistake?

Or is this a problem whis the SE 7.25?

Greeting

Ralf

[jrenaut]

Post by Ralf Hackmann
Hello,
if I code in production system the proposal from S.Kagel I get an error message
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
( artwgr and teilfam should be eq artwgr and the other fields of b.txt should be updated in an other table)
"201: A syntax error has occured
Error in line15
Near character position 34"
Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Where is my mistake?
Or is this a problem whis the SE 7.25?
Greeting
Ralf

I believe when you are updating multiple columns in your update statement, and you are using a query for the values you need another set of ()'s...so try this as your statement:

UPDATE fyar1sta
SET (artwgr, teilfam ) =( ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel ) )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

Jacques Renaut
IBM Informix Advanced Support
APD Team

[Art Kagel]

The select statement in the right side of the SET clause has to return
exactly one row and of course you want it to relate to the row being
updated, so you are missing the correlation filter for that. Try:

UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel
AND jpv.artikel = fyar1sta.artikel ) --
This filter was missing!
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by Ralf Hackmann
Hello,
if I code in production system the proposal from S.Kagel I get an error message
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
( artwgr and teilfam should be eq artwgr and the other fields of b.txt
should be updated in an other table)
"201: A syntax error has occured
Error in line15
Near character position 34"
Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Where is my mistake?
Or is this a problem whis the SE 7.25?
Greeting
Ralf
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Ralf Hackmann]

                           WHERE jpv.artikel = ar1.artikel
                                   AND jpv.artikel = fyar1sta.artikel )  -- This filter was missing!

Isn´t this the same filter as the filter in the row before?

Greetings

Ralf

[Art Kagel]

No. The filter in the line above (jpv.artikel = ar1.artikel) is joining
the temp table to the copy of fyar1sta in the sub-query (which BTW is
completely unnecessary) which is why you are returning multiple rows. You
need to FILTER the rows from the temp table using the key of the current
row in the outer UPDATE. Actually the following will be a bit faster by
dropping the join in the SET clause subquery. Didn't notice before that
you aren't using any data from the joined copy of fyar1sta:

UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by Ralf Hackmann
WHERE jpv.artikel = ar1.artikel
AND jpv.artikel = fyar1sta.artikel )

-- This filter was missing!
IsnŽt this the same filter as the filter in the row before?
Greetings
Ralf
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Ralf Hackmann]

Sorry Art, but when I run Your last quere I get the same error message

hiere my query from the production system:

CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;

LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);

UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

A syntax error has occured
Error in Line 15
Near caracter position 34

Line15:
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr

Ralf

[jrenaut]

Post by Ralf Hackmann
Sorry Art, but when I run Your last quere I get the same error message
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
A syntax error has occured
Error in Line 15
Near caracter position 34
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Ralf

So once again I'll say, add an extra set of ()'s around the right side of your set piece of the update statement (according to the SQL syntax guide if you have multiple columns to update it wants an extra set of ()'s around the select statement expression)...like this:

set (artwgr, teilfam ) = ( ( select .... blah blah ) )
where etc...

Jacques Renaut
IBM Informix Advanced Support
APD Team

[Art Kagel]

Ralf:

Jacques is correct, my fault, you need another layer of parenthesis around
the SELECT on the right side of the SET clause. One set for the
multi-column SET and another for the sub-query itself.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by jrenaut

Post by Ralf Hackmann
Sorry Art, but when I run Your last quere I get the same error message
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
A syntax error has occured
Error in Line 15
Near caracter position 34
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Ralf

So once again I'll say, add an extra set of ()'s around the right side of
your set piece of the update statement (according to the SQL syntax guide
if you have multiple columns to update it wants an extra set of ()'s around
set (artwgr, teilfam ) = ( ( select .... blah blah ) )
where etc...
Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Ralf Hackmann]

Jacques is correct, my fault, you need another layer of parenthesis around the SELECT on the right side of the SET clause.  One set for the multi-column SET and another for the sub-query itself.

Jacques an Art, thank You very much, now it works

a.txt:
00005746-0200;301;509;5
00006019-0200;302;509;5
00006835-0200;303;509;5
00007610-0200;304;509;5
00015431-0200;305;509;5

a.sql:
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,
wbzkrit integer
) with no log;

LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,
wbzkrit);

UPDATE fyar1sta
SET (artwgr, teilfam ) = (( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel ))
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );

SELECT artikel, artwgr, teilfam
FROM fyar1sta
WHERE artikel
IN ( SELECT artikel
FROM tmp_jpv );

Result:
artikel artwgr teilfam

00005746-0200 301 301
00006019-0200 302 302
00006835-0200 303 303
00007610-0200 304 304
00015431-0200 305 305

[malc_p]

Hi there
I'm concious this is an old unsupported IDS version (IDS 9.30HC5) but as
part of a migration I have to do a proof of concept.

We've got the dbserver installed on a virtualised platform (basically
it's a direct lift from $PARENT_COMPANY's installation copied onto the
target machine and the installserver/RUN_AS_ROOT.server stages run).

It initialises OK and I can create the databases and extra chunks we
need etc etc but when I come to dbimport our exported databases, it gets
so far and the we get the dreaded "Blocked:CKPT" message on onstat -u.
Trying onmode -c results in the "Checkpoint has stalled and is not
complete" message.

We're loading the dbimport with no logging mode set, and have 50 AIO
VPs, which should be sufficient. onstat -u for the dbimport thread is
showing "---PX--".

Any clues please? Yes I know this is not a standard procedure...

[Art Kagel]

What does onstat -l show? If you are dbimporting an export that was made
without the -ss option then all tables are created with minimum extent
sizing which will cause thousands of new extents to be added to the tables
during the dbimport and extents ARE logged regardless of the logging state
of the database. So, you may be running out of logical log space.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.

Post by malc_p
Hi there
I'm concious this is an old unsupported IDS version (IDS 9.30HC5) but as
part of a migration I have to do a proof of concept.
We've got the dbserver installed on a virtualised platform (basically
it's a direct lift from $PARENT_COMPANY's installation copied onto the
target machine and the installserver/RUN_AS_ROOT.server stages run).
It initialises OK and I can create the databases and extra chunks we
need etc etc but when I come to dbimport our exported databases, it gets
so far and the we get the dreaded "Blocked:CKPT" message on onstat -u.
Trying onmode -c results in the "Checkpoint has stalled and is not
complete" message.
We're loading the dbimport with no logging mode set, and have 50 AIO
VPs, which should be sufficient. onstat -u for the dbimport thread is
showing "---PX--".
Any clues please? Yes I know this is not a standard procedure...
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[Fernando Nunes]

You mentioned an HP-UX virtualized environment... Do you mean an IA64
machine emulating an older PA-RISC architecture?
If yes, it will probably not work properly... you may try to use just one
CPU VP if you're not doing that already... But you'll most likely hit other
issues...
If all this matches up, I can dig a bit more information....

By the way, I know of customers trying more or less the same with Oracle
and they also had problems... Apparently the emulation is a bit
"different"...
One thing you may try, is to disable all the optimizations introduced by
the newer architecture... things like CPU cache, pre-fetch or whatever
sounds similar... I had a situation similar to that one with a customer,
but I haven't followed the situation trough the whole process...
Regards.

Post by malc_p
Hi there
I'm concious this is an old unsupported IDS version (IDS 9.30HC5) but as
part of a migration I have to do a proof of concept.
We've got the dbserver installed on a virtualised platform (basically
it's a direct lift from $PARENT_COMPANY's installation copied onto the
target machine and the installserver/RUN_AS_ROOT.server stages run).
It initialises OK and I can create the databases and extra chunks we
need etc etc but when I come to dbimport our exported databases, it gets
so far and the we get the dreaded "Blocked:CKPT" message on onstat -u.
Trying onmode -c results in the "Checkpoint has stalled and is not
complete" message.
We're loading the dbimport with no logging mode set, and have 50 AIO
VPs, which should be sufficient. onstat -u for the dbimport thread is
showing "---PX--".
Any clues please? Yes I know this is not a standard procedure...
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

[malc_p]

Post by Fernando Nunes
You mentioned an HP-UX virtualized environment... Do you mean an IA64
machine emulating an older PA-RISC architecture?
If yes, it will probably not work properly... you may try to use just
one CPU VP if you're not doing that already... But you'll most likely
hit other issues...
If all this matches up, I can dig a bit more information....
By the way, I know of customers trying more or less the same with
Oracle and they also had problems... Apparently the emulation is a bit
"different"...
One thing you may try, is to disable all the optimizations introduced
by the newer architecture... things like CPU cache, pre-fetch or
whatever sounds similar... I had a situation similar to that one with
a customer, but I haven't followed the situation trough the whole
process...
Regards.

Yes, it is virtualized as you guess...hmm.

I'll try going to single CPU VP, but that's going to be a problem when
we try to get the production system running as it really needs all the
CPU we can throw at it!
I'll speak to their tech admins about the emulation, but it's getting
well out of my comfort zone...

Thanks

[Fernando Nunes]

I'm taking from what I remember... The situation you describe matches what
I remember. It was a problem on CREATE INDEX. That's probably why you see
the "X" in the flags... It's in a critical section. It should be in/out
instantaneously but due to a timing issue it never gets out. The checkpoint
cannot complete while there is any thread in a critical section. That's why
you see the checkpoint hang...

This, and the other issue I had, can be considered a bug in Informix...
question is, nearly no customer ever hit them on the "real thing", and it
seems that on the virtual environment it's 100% of the time. I think one of
them was solved in 9.x something... So it could help if you move to the
latest fixpack/patch ever built for that version on PA-RISC. But I'd have
to dig up some infomation to check this. As I mentioned I didn't follow up
the situation to the end... I was overloaded at the time and a Spanish
colleague took the situation. I can try to check.
But in short, given these situations and the one I heard from Oracle it
seems that the emulation is not like the real thing. I suppose you
considered moving to a newer version on IA64? It's really a waste of
technology to use IA64 to emulate old stuff (and trust me, I'm not a very
big fan of IA64).
If you have maintenance/support active, I think you could try to open a
PMR. Explain that you're trying to move... I believe Support Pilot would
apply to that situation and you could get the latest fixpack/patch for the
old platform. It's possible that would help, but tech support should be
able to find the bugs. That first one is pretty easy to match. The other
one was not that easy I believe.

Regards.

Post by Fernando Nunes
You mentioned an HP-UX virtualized environment... Do you mean an IA64
machine emulating an older PA-RISC architecture?
If yes, it will probably not work properly... you may try to use just one
CPU VP if you're not doing that already... But you'll most likely hit other
issues...
If all this matches up, I can dig a bit more information....
By the way, I know of customers trying more or less the same with Oracle
and they also had problems... Apparently the emulation is a bit
"different"...
One thing you may try, is to disable all the optimizations introduced by
the newer architecture... things like CPU cache, pre-fetch or whatever
sounds similar... I had a situation similar to that one with a customer,
but I haven't followed the situation trough the whole process...
Regards.
Yes, it is virtualized as you guess...hmm.

I'll try going to single CPU VP, but that's going to be a problem when we
try to get the production system running as it really needs all the CPU we
can throw at it!
I'll speak to their tech admins about the emulation, but it's getting well
out of my comfort zone...
Thanks

[malc_p]

Well...I tried going to single CPU VP, and it stalled almost immediately
so I thought "Oh what the hell!" and set it to 8 CPU VPs (there are 8 on
the virtualised machine), along with150 AIO VPs.
It then went a lot further than before but then just stopped. Not at a
checkpoint or anything, just all activity was dead (as shown in onstat
-u, onstat -g glo, onstat -l, onstat -g ath, anything whatsoever).
onstat -g act just showed a polling thread.

I then finally managed to get the client to set up KAIO (there was a
problem in that you can't grant MLOCK to informix in a virtualised
container on HP-UX, it has to be done globally on the host. Outside my
pay grade to understand this...) and when we finally got that running lo
and behold, it loads up fine (and VERY quick!)

So, I'd imagine there's an issue - but remember this is a VERY old IDS
but $PARENT_COMPANY is not going to be spending any money upgrading,
getting a renewed support contract and so on - with AIO on
virtualisation, somehow, somewhere...
I'm sure somewhere else along the line when we try to clobber it with
100+ users doing DSS and OLTP concurrently, something else will show up
too. But, as they say, "may you live in interesting times!"
Thanks Fernando, Art, for looking at this.

Post by Fernando Nunes
I'm taking from what I remember... The situation you describe matches
what I remember. It was a problem on CREATE INDEX. That's probably why
you see the "X" in the flags... It's in a critical section. It should
be in/out instantaneously but due to a timing issue it never gets out.
The checkpoint cannot complete while there is any thread in a critical
section. That's why you see the checkpoint hang...
This, and the other issue I had, can be considered a bug in
Informix... question is, nearly no customer ever hit them on the "real
thing", and it seems that on the virtual environment it's 100% of the
time. I think one of them was solved in 9.x something... So it could
help if you move to the latest fixpack/patch ever built for that
version on PA-RISC. But I'd have to dig up some infomation to check
this. As I mentioned I didn't follow up the situation to the end... I
was overloaded at the time and a Spanish colleague took the situation.
I can try to check.
But in short, given these situations and the one I heard from Oracle
it seems that the emulation is not like the real thing. I suppose you
considered moving to a newer version on IA64? It's really a waste of
technology to use IA64 to emulate old stuff (and trust me, I'm not a
very big fan of IA64).
If you have maintenance/support active, I think you could try to open
a PMR. Explain that you're trying to move... I believe Support Pilot
would apply to that situation and you could get the latest
fixpack/patch for the old platform. It's possible that would help, but
tech support should be able to find the bugs. That first one is pretty
easy to match. The other one was not that easy I believe.
Regards.

[malc_p]

Post by Art Kagel
What does onstat -l show? If you are dbimporting an export that was
made without the -ss option then all tables are created with minimum
extent sizing which will cause thousands of new extents to be added to
the tables during the dbimport and extents ARE logged regardless of
the logging state of the database. So, you may be running out of
logical log space.
Art
Art S. Kagel

Hi Art
no, onstat -l is showing no real usage:

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 0 16 67828 2319 336 29.2 6.9
Subsystem numrecs Log Space used
OLDRSAM 67828 4354504

address number flags uniqid begin size used %used
d76b4c90 1 U-B---- 1 10148f 5000 5000 100.00
d76b4cc8 2 U---C-L 2 102817 5000 539 10.78
d76b4d00 3 A------ 0 103b9f 5000 0 0.00
d76b4d38 4 A------ 0 104f27 5000 0 0.00
d76b4d70 5 A------ 0 1062af 5000 0 0.00

also, onstat -g act only shows the belowand never changes: (onstat -u is
showing no increases in reads or writes either):

Informix Dynamic Server Version 9.30.HC5 -- On-Line (CKPT REQ) -- Up
01:18:00 -- 372220 Kbytes
Blocked:CKPT

Running threads:
tid tcb rstcb prty status vp-class name
56 d7e2a730 0 2 running 1cpu sm_poll
57 d7f8e798 0 2 running 59soc soctcppoll