Long Transaction - How to ?

DBFinder · #1 (**permalink**) 01-19-12, 15:38

Hi Friends,

In development environment, I am facing this problem :

Code:

alter table table_audit modify table_key varchar(180) not null;


   458: Long transaction aborted.
 12204: RSAM error: Long transaction detected.

I understand that this is logs full situation. How can I avoid it temporarily.
What I mean is that the shortest possible way to get around ??

Regards
DBFinder

DBFinder · #2 (**permalink**) 01-19-12, 16:33

Even after adding many logical logs files, it is stll happening.

Code:

address          number   flags    uniqid   begin                size     used    %used
700000020394fc0  4        U-B----  7757     3:53                 2500     2500   100.00
70000002037dd20  5        U-B----  7758     3:2553               2500     2500   100.00
70000002037dd88  6        U-B----  7759     3:5053               2500     2500   100.00
70000002037ddf0  7        U-B----  7760     3:7553               2500     2500   100.00
70000002037de58  8        U-B----  7761     3:10053              2500     2500   100.00
70000002037dec0  9        U-B----  7762     3:12553              2500     2500   100.00
70000002037df28  10       U-B----  7763     3:15053              2500     2500   100.00
70000002037df90  11       U-B----  7764     3:17553              2500     2500   100.00
7000000201aa230  12       U-B----  7765     3:20053              2500     2500   100.00
7000000201aa298  13       U-B----  7766     3:22553              2500     2500   100.00
7000000201aa300  14       U-B----  7767     3:25053              2500     2500   100.00
7000000201aa368  15       U-B----  7768     3:27553              2500     2500   100.00
7000000201aa3d0  16       U-B----  7769     3:30053              2500     2500   100.00
7000000201aa438  17       U-B----  7770     3:32553              2500     2500   100.00
7000000201aa4a0  18       U-B----  7771     3:35053              2500     2500   100.00
7000000201aa508  19       U-B----  7772     3:37553              2500     2500   100.00
7000000201aa570  20       U---C-L  7773     3:40053              2500      592    23.68
7000000201aa5d8  21       U-B----  7737     3:42553              2500     2500   100.00
7000000201aa640  22       U-B----  7738     3:45053              2500     2500   100.00
7000000201aa6a8  23       U-B----  7739     3:47553              2220     2220   100.00
70000002317cea8  1        D------  0        9:954051              250        0     0.00
700000022c8a420  2        U-B----  7740     9:954739             3125     3125   100.00
700000022c8a488  3        U-B----  7741     9:957864             3125     3125   100.00
70000002555cee8  24       U-B----  7742     9:960989             3125     3125   100.00
700000024a93e80  25       U-B----  7743     9:964114             3125     3125   100.00
7000000215d9e58  26       U-B----  7744     9:967239             3125     3125   100.00
700000023aacca0  27       U-B----  7745     9:970364             3125     3125   100.00
700000022c8a698  28       U-B----  7746     9:973489             3125     3125   100.00
700000022c8adf8  29       U-B----  7747     9:976614             3125     3125   100.00
70000002555cf50  30       U-B----  7748     9:979739             3125     3125   100.00
70000002555cfb8  31       U-B----  7749     9:982864             3125     3125   100.00
7000000201aa710  32       U-B----  7750     9:985989             3125     3125   100.00
7000000201aa778  33       U-B----  7751     9:989114             3125     3125   100.00
700000024a93d90  34       U-B----  7752     9:992239             3125     3125   100.00
700000024a93df8  35       U-B----  7753     9:995364             3125     3125   100.00
7000000215d9d60  36       U-B----  7754     9:998489             3125     3125   100.00
7000000215d9dc8  37       U-B----  7755     9:1001614            3125     3125   100.00
700000023aacba0  38       U-B----  7756     9:1004739            3125     3125   100.00
 37 active, 38 total

Anyone have any idea why : do any other parameter need be changed ??

DBFinder

begooden-it · #3 (**permalink**) 01-20-12, 04:26

Hi Mr DbFinder!

I am back. There are several ways to bypass your problem.

1) Increase the logical log size by adding a big logical log with the onparams command ( onparams -a -d dbspacename -s sizeInKb ), make a pseudo level 0 archive ( ontape -L 0 with TAPEDEV temporarily set to /dev/null ), run your alter table. At the end , you can drop the logical log you just created

2) set the whole database in non logged mode with the ontape command
ontape -N databasename
run your alter table
and reset the database to the initial logging mode:
run a "pseudo level 0 archive" as above
ontape -B database name is buffered logging ( -U if unbuffered )
and here you go

3) use the RAW table functionality, which turns the named table to unlogged mode, and only this one
preferrably have a very recent backup, just in case ( risk increases with the table size...)
alter table tablename type ( RAW )
run your initial alter table
alter table tablename type ( standard )

And there you go!

The fastest is option 2, but not to be considering if any connexion on the database.
The option 3 is a good option because granularity is limited to the only table you handle, which in any case will have to be locked due to the initial alter table command.

Now, hand on !

regards
Eric

DBFinder · #4 (**permalink**) 01-20-12, 04:36

Thanks,

I will try option 3, there might be many connections, as there are few teams using this database.

Regarding option 1, I had added last 12 logs, do you mean to add one big log instead of 12 small ??

regards

DBFinder · #5 (**permalink**) 01-20-12, 04:49

Quote:

Originally Posted by begooden-it

3) use the RAW table functionality, which turns the named table to unlogged mode, and only this one
preferrably have a very recent backup, just in case ( risk increases with the table size...)
alter table tablename type ( RAW )
run your initial alter table
alter table tablename type ( standard )

BTW : Is there any command to backup table structure and data indiviually.

For example DB2 will EXPORT table structure and data to IXF file. That you can use just as backup file .

I am afraid this database is on fake backup already. Must I take a backup of whole database by dedirecting TAPEDEV to a folder. I am not comfortable with backups yet.

There must be way to backup data and table structure some how !!

Regards

begooden-it · #6 (**permalink**) 01-21-12, 03:45

Hi,

sorry for the delay I had to travel yesterday.

To backup only one table, you have several solutions, not properly callable as 'backup' but they work fine

1) the simplest one:
in dbaccess, run:

Code:

unload to filename select * from tablename

this will produce a ascii delimited file that you will we able to load after
deleting the table contents ( or use 'truncate' if in 11.70 ) , in case of necessity, like this:

Code:

load from filename insert into tablename

2) unload the table, but as binary pages format, which is faster

Code:

onunload databasename:tablename -t filename -s maxfilesize -b blocksize

and for the eventual reload

Code:

onload databasename:tablename -t filename -s maxfilesize -b blocksize

Filename must be an existing empty file, with relevant rw permissions
If you reload with onload, you have to drop the table before, because onload with load the whole table structure including its schema.
If you don't feel safe about this operation, practice in a staging environment.

3) There is another way to obtain table granularity with onarchive, but you won't have time to implement it quickly. Please let me know if you want to see more about it.

Good luck! ( well you don't need luck, you just need to go ahead :-)

Eric

DBFinder · #7 (**permalink**) 01-21-12, 05:17

Thanks

Last issue was resolved, method 3 did not work because table had indexes,

method 2 was not tried because the database was in use by other developers,

method 1 took about 2 hours, increasing logs in steps and falling each time,
had to increase temp spaces also. I could not figure out how big logs were needed - any estimate logic here ?

Finally there were 2 questions left in my mind --

1. how do we backup table structure ? ( or any tool to make copies of db objects or extract DDL )
2. how do we make script run in "stop-on-error" mode ??

Also, is there any easier command line client suitable for DBA's use ?

Thanks for your time.

DBFinder

ps: in informix do we have something equivalent to "create table abc like xyz " or "create table abc as ( select * from xyz )"

begooden-it · #8 (**permalink**) 01-21-12, 06:33

Hi,

for the method 1: figure out that alter table will create a full copy of your table, so if your table is huge, the log files will be huge too. I would estimate logical log sizes for at least the datasize ( the before image ).

I would insist on method 3, that fails because you have constraints and/or unique indexes on the table. So I think that if you drop the constraints ( + eventually unique indexes ) of this table, you can switch to RAW TYPE, and proceed as needed. You rebuild the constraints afterwards.

In any case, this operation is heavy. You will save space and time if you drop the primary and foreign key + unique indexes and rebuild them after.

For your new questions:
1) to backup table schema:

Code:

dbschema -d databasename -t tablename -ss schemafilename.sql

2) Regarding the stop on run: a script you run on dbaccess will always stop on error ( in fact there is no way to have it continue or catch exceptions )...

3) There is no "general purpose" command line tool, that would be a central point for all possible commands ( onstat, oncheck, onspaces, onparams etc... ) BUT did you take a look at Open Admin Tool ?

This is Web based GUI that centralizes all of this, provides excellent monitoring capacities, space handling, instance admin, perf analisys, cluster admin, interface for task scheduler + much more.

It comes within the distribution since 11.70 XC4, else you can find it
here

It is free, extensible and sincerly a great tool for this price.
take care!
Eric

DBFinder · #9 (**permalink**) 01-21-12, 08:53

Thanks,

OAT is the first tool that I had tried in my test lab. It is great tool.
However, I cannot use it at work; not allowd to install it.

About the third option withe original issue, it was feasible in current situation, I only avoided it for practice in regard to many other situations that will come across in near future.
e.g.

While developers are developing a new interface/application, it would be quicker or equally easy to drop and recreate the table.

While developing/extending an old thing, there may be multiple constraints/triggers/views or other related objects. I had situations in past (db2) where we had to spend a great deal of time in discovering all related objects. Even if we can do anything in DEV, we still have to deal with UAT and PROD.

Infact I did not mean "general purpose tool", instead I mean command line set of scripts/tools or an client that is for daily dba functions; but I think I should develop my own.

Thanks again,

DBFinder

begooden-it · #10 (**permalink**) 01-21-12, 09:31

Hi again,

In production, the right path would be to increase the logical logs as much as necessary: safer but much slower. Oh suddenly remembering ( stupid me!) , you can also temporarily and CAREFULLY increase LTXHWM & LTXEHWM parameters in your onconfig file if those are low and not many users are connected at this time. Don't forget to reset them after completion.

For development, as long as the database is not used in the same environment as production, the best trade-off between safety and time should definately be # 3 ( and maybe disable the constraints instead of dropping them ), then rebuild them after completion.

Dropping the table and re-creating it with the new schema can add complexity to the dataload part, specially if columns are inserted in between. Not impossible to do, but you need to dedicate time to build a customized load script.

Well that's my point of view, you hold the weel :-)

Last post for this week-end.

Have a nice one
Eric

DBFinder · #11 (**permalink**) 01-21-12, 10:30

I knew but not in favor of increasing LTXHWM & LTXEHWM parameters

As far as re-creating table; it was quite simple in current situation. Mainly depends upon complexity of relaed objects.

Enjoy your weekend; and to let you know, your responses has built rock-solid confidence in me.

Regards

DBFinder · #12 (**permalink**) 01-22-12, 11:36

Quote:

Originally Posted by begooden-it

2) Regarding the stop on run: a script you run on dbaccess will always stop on error ( in fact there is no way to have it continue or catch exceptions )...

Code:

[root@a2centos64 ~]# dbaccess test sql.sql

Database selected.


1 row(s) inserted.


  391: Cannot insert a null into column (test.id).
Error in line 2
Near character position 28

1 row(s) inserted.



         id

          3
          5

2 row(s) retrieved.


Database closed.

[root@a2centos64 ~]#

sql.sql

Code:

[root@a2centos64 ~]# cat sql.sql
insert into test values(3);
insert into test values(null);
insert into test values(5);
select * from test;

table test

Code:

[root@a2centos64 ~]# dbschema -t test -d test

DBSCHEMA Schema Utility       INFORMIX-SQL Version 11.70.FC4IE







{ TABLE "root".test row size = 4 number of columns = 1 index size = 0 }
create table "root".test
  (
    id integer not null
  );

revoke all on "root".test from "public" as "root";

By using -a you can make it stop on error

Quote:

[root@a2centos64 ~]# dbaccess -a test sql2.sql

Database selected.

1 row(s) inserted.

391: Cannot insert a null into column (test.id).
Error in line 2
Near character position 28

Database closed.

[root@a2centos64 ~]#

begooden-it · #13 (**permalink**) 01-22-12, 15:31

I was meaning "an sql statement", not a full script containing several statements

But anyway, I didn't know about the -a option. Well done!

If you need more granularity/precision for errors handling, this is the right time to use Perl DBI::Informix
which basically has no limits in terms of scripting...

Cheers
Eric

DBFinder · #14 (**permalink**) 01-22-12, 18:25

Exactly , I need perl now.

If it is really easy (quick ), Please show me the simplest perl DBI example where I can
1) connect to database
2) run select statement
3) run insert statement
4) run alter table statement
5) disconnect from database

Just if it does not take much time.

I am starting perl programmin now . . .

Cheers

begooden-it · #15 (**permalink**) 01-23-12, 09:58

Hi,

you can find some basic examples of perl with informix to be downloaded from here

Else, if you find someone that can read french ( not so difficult from where you are, else translate.goggle.com ), I can recommand this article as a good starter, else this one in English

Internet Services

Web Development

Digital Marketing

Consumer Tech