November 09 2011

How to test INSERT/UPDATE in Informix BYTE column?

Tagged Under : , , , , , , ,

Question:
How to test INSERT/UPDATE in Informix BYTE column?

Preparation:
1. Informix IDS is online and running

2. Informix Client-SDK installed

3. Prepare 2 object files (image1.pdf, image2.pdf)

4. create table testbyte1 (num integer,
file byte);
create unique index byte_idx1 on testbyte1(num) ;

5. Compile “blobload” utility.
– set environment variable $INFORMIXDIR
(where CSDK installed)

– set environment variable PATH=$INFORMIXDIR/bin:$PATH
– you may find “blobload.ec” in $INFORMIXDIR/demo/esqlc
– compile “esql -o blobload blobload.ec”, will get executable binary “blobload”

Test INSERT/UPDATE BYTE column:

6. To get syntax on usage, just type “./blobload”

Usage: blobload {-i | -u} — choose insert or update
-f filename — file containing the blob data
-d database_name — database to open
-t table_name — table to modify
-b blob_column — name of target column
-k key_column key_value — name of key column and a value
-v — verbose documentary output

All parameters except -v are required.
Parameters may be given in any order.
As many as 8 -k parameter pairs may be specified.

7. INSERT BYTE testing.

$ ./blobload -i -f ./image1.pdf -d mytestdb -t testbyte -b file -k num 1001 -v

prepared stmt =
INSERT INTO testblob1( file, num) VALUES( ?, “1001”);

1 row(s) inserted.

8. UPDATE BYTE testing.

$ ./blobload -u -f ./image2.pdf -d mytestdb -t testbyte -b file -k num 1001 -v

prepared stmt =
UPDATE testblob1 SET file = ? WHERE num = “1001”;

1 row(s) updated.

9. Check number of rows:

SQL> select * from testbyte1;

num 1001
file

1 row(s) retrieved.

October 24 2011

Informix: Shall we plan to backup logical logs on the RSS instances?

Tagged Under : , , ,

Question:
We want to initialize high-availability replication (HDR) and/or Remote Standalone Secondary (RSS) replication. Should we plan to backup logical logs on the secondary server instance(s)?

Reference URL:
http://www-01.ibm.com/support/docview.wss?uid=swg21516867&myns=swgimgmt&mynp=OCSSGU8G&mync=E

Applies to:
IDS 11.1, 11.50, 11.70

Answer:
The secondary server instances are in a continuous log apply status. You cannot execute a logical log backup on an HDR or RS secondary server.
This applies regardless of whether it is a read-only or updatable secondary.

The ontape and onbar logical log backup commands plus the message returned:

ontape

$ ontape -l
Logical restore failed – A Logical Restore is already in progress.

Program over.
onbar

$ onbar -b -l
DR: This command is not valid on a secondary server.

October 24 2011

Setup another Informix instance on the same machine

Tagged Under : , , , ,

Question:
How do you set up another Informix Instance on the same machine?

Reference URL:
http://www-01.ibm.com/support/docview.wss?uid=swg21504585&myns=swgimgmt&mynp=OCSSGU8G&mync=E

Objective:
Steps to setup another instance (same or different version) on the same physical machine.

Steps:

1. Write down the dbservername (INFORMIXSERVER), INFORMIXDIR, PATH,ONCONFIG, servernum,port number,rootpath, mirror path,message path, and any other relevant information that you want to have unique.

2. If you are installing a new version: create the INFORMIXDIR for the new instance. Make sure that is a different one from the one you have
for the first instance so you don’t install the files over your existing ones. If you are using the same version of informix and want to use that same INFORMIXDIR you can do that. In this case, you don’t have to make sure INFORMIXDIR is different or copy any new files over (skip step 3).

3. cd to that directory and install the product (ensure you are logged in as root. Then follow directions for that version as to who you should
be logged in as- informix or root). Run installserver files as appropriate

4. Make a setup file with new INFORMIXDIR, PATH, INFORMIXSERVER, ONCONFIG, and path to the sqlhosts file and source the setup file. Make sure env shows the right variables.

5. Edit your /etc/services file to add the entry for your new dbserver TCP/IP connection. The service name and the port number must be unique in the file. I would suggest putting this next to your other informix entry to keep them together.

6. Add an entry in your same sqlhosts (you only need one) for the new database server, including the new service name you just added.

7. Copy over your onconfig.std to the path you have your new ONCONFIG environment variable pointed to. If you copy over your onconfig from the
first instance you run the risk of losing any newly added ONCONFIG parameters to the newer version.

8. Customize your onconfig so that the new rootpath, mirrorpath, message path, servernum, dbservername, dbserveraliases, and any other relevant information is DIFFERENT from your first onconfig. THE ROOTPATH IS VERY IMPORTANT SO THAT YOU DON”T OVERWRITE THE FIRST INSTANCE’S DATA!!! Also keep in mind the servernum is used to calculate a relevant location in shared memory. The value you choose is not important except that it must be unique for each database server on your local host computer. Make sure this is different from your first instance or you will get an error message upon initialization.

9. Touch any cooked files you need to have for the rootdbs and make sure they have the right permissions (informix informix 660).

10. Do another env to double check your environment; make sure INFORMIXSERVER is pointing to the new server and ONCONFIG is the path to the new onconfig file.

11. When ready do an oninit -ivy! This will initialize the disk (not just the memory) for the first time and will wipe out any existing data. This is why you must make sure everything is pointing to the new instance so the right root chunk is initialized. Any other time after the first time, you bring up the instance with oninit and any other flags BUT the -i option!

12. Now you can recreate the database or data if wanted by using dbimport or any other migration utility.