Hi

I am an Oracle DBA , new to Informix. i have an assignment to be completed within 15 days. My request is :

There is one IDS9.2 production server running in HP -UX platform.
tasks are :

1. Installing Informix Dynamic server in HP-UNIX.
2.Creating database structure based on production database.
3.replicating the production database structure in the new database without data( only DDL is needed).

I need any one of your help in this.
Appreciate your help.


thanks

Sundar

Hi Sundar,

This seesm like a rather simple task that should take a newbie (to Informix) not more as 2-3 days. Just some info on the steps:

1. Just follow the installation notes (which you can download from www.ibm.com/software/data/informix) On the left there must be a documentation link.

2. Just recreate the spaces. Also copy te onconfig ($ONCONFIG and sqlhost file) from the production.

3. The easiest step. Just specify the following command on the production system: dbschema -d <dbname> -ss create_ddl.sql
Next step is to create the database on the second machine:
create database <dbname> <log specification> IN <dbspace>
After that: run the create_ddl script in dbaccess on the second machine.

If you have any other question, just ask on the forum. I think you will get an answer rather fast.

Some ectra info: IDS 9.2 is rather old and not supported anymore. Is the customer aware of this??

Greetz,

Rob

Hi
Thanks a lot for your kind help.
I have got some 75% idea from your mail.
still i have some doubts.
1. how many user can we create in a single informix database?
someone told here that only one user can be created in a single database.
2. In which user all the tables will be created and how can differentiate tables from one user to another user.
normally in Oracle, we can prefix username with tablename.
what is the convention here?

3. how to identify all the dbspaces created in a DB?
In the DDL file, the table creation script may have dbspace where it has been created in the production db.
In this how can i proceed ?
do we need create all the dbspaces as in production db?,
or can we create our own dbspace?
please help me.

Appreciate your kind help.


Thanks a lot , once again

Sundar

Hi Sundar,

Here are some ansers to your questions:

1. Unlimited ?
In Informix you don't really create users. You just specify rights to users. But if for example you specify, grant dba to plublic (Something I dont promote however) Then everyone can log in after authentication. The authentication part in IDS is left over to the OS. So all db users should be OS users. For the rest no limitation.

2. Normally you specify one user to create the tables. By default we don't support schema's like oracle is using. We don't have the need for that. Often groups of tables are grouped in oracle with a schema, we do this with databases. Remember in IDS a database is nothing then a colletion of objects. Storage adn back-up end so on are focused on a instance level not a databaselevel.
But if you wishes to use schemas, you can use an ANSI MODE database. Then it workd globally the same as in Oracle. Default isolation level is in this case repeatable read!
Normally we try to avoid ANSI databases, because we don't like repeatable read and haven't got the need for schema's. We often see Oracle DBA's use schema due to lack of proper black box database definition ;-)
3. onstat -d give you a list of all dbspaces. If you don't use the -ss option in dbschema then you can place all data in a single dbspace (supposing you don't need smart blobs)

Greetz,

Rob

Hi Rob
Thanks a lot for your kind help , Mr.Rob.
few more questions, sorry to trouble you.

1. As you said , any OS user can access any database?, right?

2. When you create a database through 'dbaccess'' like
CREATE DATABASE <db_name> IN <dpspace_name>
here we are specifying the dpspace name, this may be the root dpspace for the database , right?. is it possible to add one more dbspace to the same database where other objects may reside?

3.suppose , is it possible for informix (OS user) can create any no.of databases? or one OS user can create only one database?

4. $dbschema -d <db_name> -ss <output filename>
As you told, since we specify -ss option in dbschema while generating the DDL from the production DB, so we donot need to create other dpspaces as in production DB( as we see (onstat -d) in the production DB) other than the default dbspace when we run the DDL script in the new database, right?

5. how to find that how many databases has been created for particular OS user. Incase, more DB is allowed per OS user , how many databases can be active at a time?


please help me, Rob.

once again I thank for your kind help.


sundar

Hi ,

1)If you have not revoked the permission from public, then any OS user can access the db.
2) Yes, it is advisable also to create db in dbspace other than rootdbs. You can create the dbspace using onspaces command.
3)Yes, OS user can create multiple dbs.
4)Yes,you dont need specifically those dbspaces as on production db if you dont use -ss option.
5)You can query the sysdatabases table in sysmaster db and find out the databases residing on the instance along with their owner names.

Hope it helps.

Nitin

Hi
Thanks a lot for your kind help.
need a few more clarification. sorry to trouble you.

1) What is the role of user 'public' in database?
suppose ,if we revoke the permission, is it possible for any user can access the database?
for example.
$dbaccess
>connect <db_name>
>grant select on table1 to sundar;
>revoke dba from public;

in this case, is it possible for sundar to access table1?

2)
$dbschema -d <db_name> -ss <output filename>
this command will generate the ddl including the dbspaces details.
my question is:
if i run the script , whether dbspaces get automatically created as the script has all the DDL (including dbspace script)
or do we have to manually create those dbspaecs?.


3)
Q:
how to find that how many databases has been created for particular OS user. Incase, more DB is allowed per OS user ,
how many databases can be active at a time?

your answer:
You can query the sysdatabases table in sysmaster db and find out the databases residing on the instance along with their owner names.

now the doubt is :
As you said , we can create any no.of databases, but my question is,
how many databases can be active at the same?, whether all can be active or only one can be active?

4.could you give some idea about the installation procedure.
my requirement is :
4GL, 4GL RDS, SQL, then IDS Engine, and then you should install the network components - SDK


thanks

sundar

Hi Sundar,

1) Public refers to all users(OS). If grant select to sundar and revoke dba from public, then he cant access the db but if he is the owner of db then he can access the table.
2)No, dbspaces wont be created automatically, while you are running the import, you will receive the error. You need to create the same named dbspaces if you are using -ss option while dbexport.
3)All databases can be active.
4) What versions?

Hi Sundar,

Just some extra info:

1. Public is the keyword that is reserved and is the same as everyone. Typical for Informix is that you can only revoke rights that are granted. What I mean with this is that it oif you give the following commands:
grant connect to public;
revoke connect from sundar;
The user Sundar can still connect to the database, because public means everyone. On the other handif you specify:
grant connect to sundar;
revoke connect to public;
In this case the user sundar can connect to the database, revoking rights for public only deletes this specific right.

2. You have to create the dbspaces yourself. This can be done with the following steps:
For using raw devices:
a) Prepare raw-device with volumemanager or any other tool
For file system (so called cooked files)
a) Create an empty file (for example: toch <filename>
For all systems (after step a)
b) make sure device/file has access rights 660
c) make sure device/file has owner and group informix
d) create dbspaces with the following onspaces command:

onspaces -c -d <dbspacename> -p <absolute path + file/devive name>
-o <start position in file/device could be 0>
-s <size in kb, must be dividable by the pahgesize which is 2kb in HP-UX>

3. One instance can have 21 million databases, I believe at most 32.000 or so can be active at the same time. But I don't think that's causing you any problem ;-)

4. Normal approach is:
- Tools (4GL's ISQL) and start with the lowest version. If version is the
same, the order doesn't matter
- IDS
- CSDK / Connect

Hope this helps,

Rob

Hi Rob,
Thanks a lot for your kind help.
I am clear with answers.
one more question.

Here the informix version is IDS 9.2 version.
People are asking me to install IDS 9.2 vesrion in this order.
4GL, 4GL RDS, SQL, then IDS Engine, and then you should install the network components - SDK.

is it necessarily to do it in the same order.
if so, could you tell me the minimal way to do this installation on HP - UX 11i.

I am sorry to trouble you , Mr.Rob.


thanks a lot

Sundar

Hi,

The order is:
- Tools first (4gl's , isql) oldest verions first
- IDS
- CSDK / IConnect

Greetings,

Rob

Hi Mr.Rob,
Thanks a lot for your kind help Mr.Rob.


With wishes


Sundar