System Monitoring Scripts 2

[mynwo]

Hi all,

Does any one have scripts to find out in which dbspace my database/tables are created. In which dbspaces my index are created and some additonal scripts for monitoring the server. Also is there any GUI tool to monitor the database.
Can anyone suggest a good book for Informix System Administration.

Regards,

lloyd

 

[vpshriyan]

Hi,

database sysmaster;
set isolation to dirty read;

-- following sql maps dbspaces to databases
select dbinfo('dbspace',partnum) dbspace, name, owner
from sysdatabases ;

-- following sql maps tables/indexes to dbspaces
select tabname,
trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from systabnames, sysdbspaces
where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576) ;

-- here you will find hundreads of scripts, including scripts to monitor instances, databases etc.

http://www.iiug.org/software/index_all.html

Informix bundled a gui "onperf" monitoring tool in certain platforms like HP (Motiff), Solaris, Linux etc.

-- free online documents in pdf formats can be found here, including informix system administration.

http://www-3.ibm.com/software/data/informix/pubs/library/

Regards,
Shriyan
"You can achieve the most when you don't care who gets the credit"

 

[mynwo]

Hi Shriyan,

Thanks once again for your prompt reply. Could you suggest a good book on Informix System Admin where we can get a list of all the commands & scripts.

Regards,

lloyd

 

[vpshriyan]

lloyd:

The Informix Handbook By Ron Flannery, published by Prentice Hall is one of the highly regarded book. It covers:

. Core Concepts and History of Informix
. Informix SQL
. Server Administration
. Performance Tuning
. Application Development
. Object-Relational Databases and the Web

More info:

http://www.InformixHandbook.com

I feel that, Informix official documentations made availble at:

http://www-3.ibm.com/software/data/informix/pubs/library/

are ultimate guide or bible sort to say, for all informix fraternity. I agree to a point that books authored by others, may contain certain author gained special capabilities, experiences & situations explained in certain lucid manner in their books.

Regards,
Shriyan
"Life is a test, if this had been a real life you would have been given instructions on where to go."

 

[mynwo]

Hi Shriyan,

Thanks once again, you have been a great help. One more query on input within a select statement. In oracle we have an sql select count(*) from tabname &1; this will accept table name as input from the user and display the no of rows in the table. Do we have a similar sql in informix.

Regards,

lloyd

 

[vpshriyan]

lloyd:

Thanks for the compliments.

I'm sorry to say that informix does not support inputs within a select statement. A sort of workaround would be to emulate such syntax using OS capability would look like below:

Contents of file name R
echo "select count(*) from $1" | dbaccess testdb
chmod 500 R

execute example:
R tab1

Regards,
Shriyan
"The hidden flaw never remains hidden."

 

[mynwo]

Hi Shriyan,

Thanks once again mate, you have been a great help.

Best Regards,

lloyd

 

[mynwo]

Hi Shriyan,

Hope things are going on well at your end, i need you advice on ontape /onarchive troubleshooting.
I have ids 9.4 installed on win nt and ids 9.21 installed on unix on sun solaris. I want to use ontape to backup & restore data. I do not have any tape device.
In win nt we cannot specify a file as a tape device, but in unix we can give a file path. Ontape -s takes the entire backup. I would like to know how can i do
troubleshooting, like i take a backup and then i restore and see if the backup was done properly. After taking a backup can i drop a database or a dbspace
and run ontape -r will it restore the database / dbspace for me or not. In case of dbspace will i have to create it first or it will be done automatically.
For eg. I have 2 database demo and stores, now i run ontape -s, it should backup the enitre instance, now i drop the demo database. Now if run ontape -r can
i recover the demo database, samething for dbspaces too. Please advice.

Regards,

lloyd

 

[vpshriyan]

lloyd:

Yeah, things are going on well at my end, except that there is general slump in all types of markets, here in India.

If you are planning to move the backed up data from Windows to Unix environment and vice versa, using either ontape or onbar utility this may not work, owing to the various factors like different architecture, page size, etc. However, should such requirement arise, you can rely on dbexport & dbimport utilities.

If you do not have any tape device you can still back your data to disk file in both Windows and Unix environments. Make sure that the target file is existing, owner is informix and file has read,write permissions. However, in such scenario the existing data file will be overwritten, each time, you losing the earlier data. Hence, there is need to move or copy the file each time. You can get rid of these and other disadvantages by ordering a tape drive.

If you are sure that your backup data is free of any inconsistency, you can certainly rely on it. You can assure this by running following commands as part of consistency check, before taking a 0 level system archive:
oncheck -cr
oncheck -cc
oncheck -ce
oncheck -cI databasename
oncheck -cD databasename

If you drop a database and initiate a restore process for example using ontape -r the dropped database will be retored properly and automatically. However, if a dbspace or chuck becomes inaccessible due to any reason, the restore process will not gather the lost object automatically. You need to touch or create the relevant chunk path with filename. Also, make sure about the proper ownership and file access mode. Once this been done, you can proceed with restore process.

Regards,
Shriyan
"When you dial a wrong number, you never get a busy signal."

 

[mynwo]

Hi Shriyan,

Good to hear that you are doing well, thanks once again for your prompt reply. I have a test ids 9.4 server on win 2000 & ids 9.2 on sun solaris. I just want to test on backup / restore, i don't want to move any data. In 9.4 the storage manager comes bundled with the engine, but since i do not have any tape device, can i give a file path name for the tape device, i tried giving one but it did not work, so how can i take a backup on win 2000. As for sun solaris as you said we can take a backup on a file, i will follow the steps what you have said. Thanks once again, have a nice day.

Best Regards,

lloyd

 

[vpshriyan]

lloyd:

The steps involved in taking 0 level system archive into a disk file in Windows environment are same as that applicable in Unix systems. In config file - %INFORMIXDIR%\etc\onconfig.%INFORMIXSERVER% you need to set an existing path for a target file under parameter TAPEDEV

The owner of this taget file should be informix and must have read and write permissions for user informix. To take a 0 level system archive backup execute ontape -s -L 0 and follow the instructions.

Regards,
Shriyan
"Man who knows, knows, he knows nothing!"

 

[mynwo]

Hi Shriyan,

Thanks once again for your help. I will follow your instructions.

Regards,

lloyd

 

[mynwo]

Hi Shriyan,

I did try out your instructions, it worked fine, i could backup to disk file on Windows. Thanks once again.

Regards,

lloyd

 

[vpshriyan]

lloyd:

You are welcome. You may have a look at the link below, which lists table of contents for Informix Dba Survival Guide. You may find this information useful and relevant.

http://www.isbn.nu/toc/0130796239

Regards,
Shriyan
"Since we cannot get what we like, let us like what we can get.