Informix Views

Views

A view is a logical representation of physical columns from one or multiple tables. A view looks and acts like a table, but it really isn't a physical table that resides on disk. Referred to by Informix as a virtual table, a view is a great way to present specific information to specific users, without placing an entire table's data in the open or keeping multiple versions of the data for different groups of users. Figure 16.1 shows how users view data from single or multiple tables through a view.

Views provide a means to restrict certain columns from users. Sometimes tables contain tons of information that is useful to only a small segment of users. Most of the time, only one or two columns are actually accessed. A view can show and allow access to those few columns. This method makes the tables seem less overwhelming to users who don't fully understand all the data columns.

Some data contained in a table may be sensitive to specific users for legal or moral reasons. You can use a view to allow users access to some of the data contained in sensitive tables while restricting access to other columns. For example, an employee table might contain information on each employee's address, phone number, who to notify in case of an emergency, and salary. Obviously, employees should not have access to other employees' salaries, but contact information could be important.

Views can represent derived values as easily as they can show stored column data. Any computations allowed by SQL can be represented in a view with a virtual column. For example, you can create a current balance column in a view to represent the sum of all current orders, subtracting any payments received. Every time the database makes an order or payment, the balance column in the view is updated.

You can represent derived data values within a view. By using the columns from one or more tables, the data placed in a view can be a derived value by using additions, subtraction, or any other mathematical function. For example, you can create a view showing the items, quantity, and value of orders placed by the customer Joe's Pizza, where the value is equal to the quantity multiplied against the price.

Creating a View

To create a view, the user attempting the create must have at least select privileges on all the tables with columns that are represented in the view. The two parts to the view creation statement are view naming and column selection.

A view's name must be a unique name of up to 18 characters. This name is used to access the view after it is created:

CREATE VIEW view_name AS

To assign columns to a view, use a standard SQL SELECT statement. The keywords ORDER BY and UNION are not allowed within a view's select statement, but all other select keywords are allowed.

Data type values are automatically inherited from the original columns to the new view columns. The names of the columns can also be inherited to the view unless specified as something different. When creating a virtual column, you must specify a name for the new column. When a column name is specified, the CREATE VIEW statement requires that all column names be specified, regardless of whether the names are different:

CREATE VIEW view_name (column list) AS

SELECT columns FROM tables

The first example sets up a standard view for the addresses of all employees who are currently employed full-time. No columns are renamed, so no column list is required.

CREATE VIEW empl_address AS

SELECT name, street, city, zip

FROM employee_info

WHERE current = Y AND

work_status = F

The next create sets up a view for all customers' ordering and payment activity. A virtual column is also created. You must list all columns because the virtual column needs a name. This view also joins two tables to retrieve the information:

CREATE VIEW customer_bal

(cust_id, last_order, last_payment, current_balance) AS

SELECT cust_id, total_order, total_payment,

total_order - total_payment

FROM order_table, payment_table

WHERE order_table.cust_id = payment_table.cust_id

You can use other views' columns to build other views. The next create example determines the total outstanding balance owed to the company from the customer_view view. You can also use aggregate SQL commands to create virtual columns. Aggregate commands include SUM, MIN, MAX, AVG, and COUNT. The SUM command is used to add all the balances together:

CREATE VIEW total_balance (balance) AS

SELECT SUM(current_balance)

FROM customer_view

The next create example sets up a view on all the column data related to specific rows. All sales made by salesperson 12, Joe, are listed in the view:

CREATE VIEW joes_sales AS

SELECT *

FROM sales

WHERE sales_person = 12

Accessing and Using Views

The creator of a view is considered the owner; owners and DBA-level users can grant and revoke access to the view to other users. You can restrict access to an entire table but give users access to the table's data through a view. This forces the users to use the view to access the data.

To restrict standard users from accessing the entire employee table but still allow them to access their addresses, you use the following example:

REVOKE ALL ON employee_info;

CREATE VIEW empl_address AS

SELECT name, street, city, zip

FROM employee_info;

GRANT SELECT, UPDATE ON empl_address TO PUBLIC;

Working with a view is just like accessing a table. Use the view name instead of the table name in all SQL commands. Some restrictions related to views are not found with individual tables. First, no indexes can be created on a view. Any table-related indexes are used when accessing the indexed data through a view. Any columns or tables used in a view must be present. If a table or view is dropped, any views that use the table or column are also dropped. Views that contain joins or aggregates can be accessed only with SELECT statements because a join or aggregate view takes different data items from different places and makes it look like it's all from one place. Informix cannot determine how a change to data in a view relates back to the original tables. Almost the same situation applies to virtual columns; because virtual columns are derived from multiple data sources, it is impossible to insert or update that value in a view. It is possible to delete the row from a view that contains a virtual column because Informix can trace back to the original column and keys.

Check Option

As mentioned in the previous discussion on creating views, you can create a view with information related to a specific data value or row. As in the joes_sales example, a view can contain a subset of a table or table's data.

The joes_sales example showed the following code:

CREATE VIEW joes_sales

(sales_person, customer, sub_length, price) AS

SELECT *

FROM sales

WHERE sales_person = 12;

If this view is available for Joe to use, he might want to insert his new sales directly through this view rather than use the entire sales table. If Joe sells newspaper subscriptions and he makes a sale of a one month subscription to Mary Jones at $9.99, this information is placed in the sales table through the joes_sales view:

INSERT INTO joes_sales

VALUES (12, "Mary Jones", 1, 9.99);

If Joe makes a mistake and uses the wrong sales_person number, his sale is credited to someone else:

INSERT INTO joes_sales

VALUES (11, "Mary Jones", 1, 9.99);

Although he uses joes_sales, the insert for sales_person 11, Jake, succeeds back to the sales table. Joe can check his view:

SELECT * FROM joes_sales;

The entry for 11 does not show up because the view is limited to sales_person 12. Jake can see the entry if he has his own view:

SELECT * FROM jakes_sales;

Users with direct access to the sales table can also see the entry:

SELECT * FROM sales;

To prevent this problem, use the keyword WITH CHECK OPTION when creating the view. The WITH CHECK OPTION allows inserts, updates, and deletes to occur only when they meet the view select criteria:

CREATE VIEW joes_sales

(sales_person, customer, sub_length, price) AS

SELECT *

FROM sales

WHERE sales_person = 12

WITH CHECK OPTION;

When Joe tries to insert his sales with the wrong sales_person number, he receives an error message.

Dropping a View

An owner of the view or a DBA can drop an existing view. When you drop a view, you do not lose the data, columns, and tables; only the view to that data is gone. The data still resides in the underlying tables and columns of the database. On the other hand, if the actual tables and columns are dropped, any views that use those tables and columns are automatically dropped. In views such as joes_sales, if Joe has no sales in the sales table, the view continues to exist, but it contains zero rows.

To drop a view, use the DROP VIEW command:

DROP VIEW view_name;

The following example uses DROP VIEW:

DROP VIEW joes_sales;

Changing a View

You cannot use an ALTER to change the layout of a view. If you need to change a view layout, you must drop and re-create the view with the new layout.

To verify the current view layouts, use the sysviews and sysdepends system tables. The sysviews system table contains the actual CREATE VIEW statement originally entered.

To see all the views currently in the database server, use

SELECT * FROM sysviews;

The sysdepends system table contains information on each view and the tables or other views that provide the data that makes up the original view.

To see all the dependencies each view contains, use

SELECT * FROM sysdepends;

When a view is dropped, its information no longer resides in the sysviews or sysdepends tables. It is a good idea to save a copy of the preceding queries as a backup listing of all the views to use as a reference when creating or re-creating a view.

Read More......

Informix Functions

Stored Procedures and Triggers

Stored procedures and triggers are two essential functions that make a successful database server and also a successful database management system. Not only do stored procedures offer a viable means to reduce processing and network traffic, but they also help remove general and repetitive functionality from the different client applications.

Stored procedures are considered separate database entities, and because they are separate, users must have the appropriate privileges to create, edit, and execute them. What is nice about stored procedures is that they can have access to specific areas of the database that users are not able to see. However, these same users might have the ability to run the stored procedure, which in turn performs specific functions in restricted areas. Therefore, the stored procedure enables users to go into restricted areas, but does not let them have full access to run wild. For example, a table contains all employees' salary and bonus information. A stored procedure is executed when a user enters information about a sale that earned commission. The stored procedure checks to see whether it's a valid commission, and then adds that amount to the appropriate person's salary. The user has no access to the table that contains the salary information, and if he or she tried to add the commission to the table without using the stored procedure, or perform any other activity on the table, it would fail.

Triggers provide the means to automatically process a task when other events occur in the database, such as specific data access or creation. By combining triggers with stored procedures, you can build a library of processes to manage data security and auditing. Chapter 14, "Managing Data with Stored Procedures and Triggers," describes in detail how to use stored procedures and triggers to manage security.

Read More......

Informix Privileges

Privileges

Three levels of data-related security keep database users (users who must have some type of access to data in the database) from accessing specific data items. These levels are database, table, and column.

Database-Level Privileges

All users must have access to a database to use data within a server. The three database-level privileges are connect, resource, and DBA. Table 16.1 shows the different authority levels associated with each privilege.

Table 16.1. Database-level privileges.

Privileges		Connect	Resource	DBA
Select, insert, update, delete, use temporary tables, and use views.		Yes	Yes	Yes
Create, alter, drop, and index own tables.		No	Yes	Yes
Grant, revoke, drop other owned tables, and start and stop server.		No	No	Yes

Connect

The minimum database-level privilege is the connect level. Users with connect can perform select, insert, update, and delete statements, run stored procedures against tables, create views on tables, and create temporary tables with or without indexes.

Resource

Users with resource privileges have all the privileges of connect users. They also have the added ability to create, alter, and drop their own tables and place indexes on these tables.

DBA

The creator and owner of a database is automatically given DBA privileges. A DBA has the same privileges as the connect and resource users with added abilities. The added abilities include granting and revoking connect, resource, and DBA privileges to and from other users, and dropping and altering other users' tables and views. Users with DBA privilege can also drop, start, stop, and recover the database.

Granting and Revoking Database-Level Privileges

The user who creates the database is automatically given DBA privileges, which is the only level that can perform grants and revokes. The first DBA can create other DBAs with a grant statement in SQL. A grant gives authority to specific users at whatever level you choose. The DBA can also use a revoke to remove or lower the authority.

Informix has a keyword called PUBLIC that represents all users who access the database server. To specify users, use their UNIX IDs. You can specify a list of users by separating each UNIX ID with a comma.

The database to which users get access is the database to which the DBA is connected when running the SQL to perform the grant. If the database server has multiple databases, the DBA must perform a grant for each database to provide access to them all. If the user is allowed access to only one of the available databases, you perform the grant within only that specific database when it is open.

To grant connect privileges, use this syntax:

GRANT CONNECT TO PUBLIC;

GRANT CONNECT TO user1;

GRANT CONNECT TO usera,userb,userc;

To revoke connect privileges, use this syntax:

REVOKE CONNECT FROM PUBLIC;

REVOKE CONNECT FROM user1;

REVOKE CONNECT FROM usera,userb,userc;

To grant resource privileges, use this syntax:

GRANT RESOURCE TO PUBLIC;

GRANT RESOURCE TO user1;

GRANT RESOURCE TO usera,userb,userc;

To revoke resource privileges, use this syntax:

REVOKE RESOURCE FROM PUBLIC;

REVOKE RESOURCE FROM user1;

REVOKE RESOURCE FROM usera,userb,userc;

To grant DBA privileges, use this syntax:

GRANT DBA TO user1;

GRANT DBA TO usera,userb,userc;

To revoke DBA privileges, use this syntax:

REVOKE DBA FROM user1;

REVOKE DBA FROM usera,userb,userc;

It is not a good idea to grant DBA privileges to PUBLIC. Imagine giving hundreds of users the ability to drop the database! When initially granting privileges, remember to grant only connect or resource levels to PUBLIC.

Table-Level and Column-Level Privileges

When a user has access to a database, the DBA can limit access to specific tables and columns within tables. The creator of the table or any resource-level or DBA-level user can create tables. That owner or any DBA can grant table-level privileges to other users for that table. A total of eight keywords provide different table-level privileges: insert, delete, select, update, references, index, alter, and all.

Insert

Granting insert privileges allows users to add new data to the table. Revoking that privilege stops users from adding data to the table.

GRANT INSERT ON customer_table TO user1;

REVOKE INSERT ON customer_table FROM PUBLIC;

Delete

Granting delete privileges allows users to remove data from a table. Revoking that privilege stops users from removing data from the table.

GRANT DELETE ON customer_table TO user1;

REVOKE DELETE ON customer_table FROM PUBLIC;

Select

Select privileges can be granted at the table level or at specific column levels. Users can have the ability to query an entire row in the table or just specific fields. In the first example, user1 can look at any column or any row of the customer_table. The second grant only allows PUBLIC to query only the customer_id and balance columns of the customer_table. You can revoke privileges in the same way.

GRANT SELECT ON customer_table TO user1;

GRANT SELECT (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE SELECT ON customer_table FROM user3;

REVOKE SELECT (customer_id, balance)

ON customer_table FROM user4;

Update

You can grant update privileges at the table level or specific column levels. Users can have the ability to change an entire row in the table or just specific fields. In the first example, user1 can update any column or any row of the customer_table. The second grant allows PUBLIC to update only the customer_id and balance columns of the customer_table. You can revoke privileges in the same way.

GRANT UPDATE ON customer_table TO user1;

GRANT UPDATE (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE UPDATE ON customer_table FROM user3;

REVOKE UPDATE (customer_id, balance)

ON customer_table FROM user4;

References

You can grant users the ability to force referential constraints on the entire row or specific columns of a table. The user must be a resource database-level user before the references privilege works. Referential constraints perform tasks such as cascading deletes or any other task that relies on how columns relate to other columns.

GRANT REFERENCES ON customer_table TO user1;

GRANT REFERENCES (customer_id, balance)

ON customer_table TO PUBLIC;

REVOKE REFERENCES ON customer_table FROM user3;

REVOKE REFERENCES (customer_id, balance)

ON customer_table FROM user4;

Index

The index privilege grants users the ability to create and drop indexes related to a table. Users must have the resource privilege in combination with the index privilege. Users with connect cannot create an index, even if they have the index privilege. There is no column-level privilege because indexes are built on all table rows.

GRANT INDEX ON customer_table TO user1;

REVOKE INDEX ON customer_table FROM user3;

Alter

The alter privilege allows users to change the layout of the columns within the table. Users with alter can add, delete, and change columns and the column data types. Only users with knowledge of the database system and how to protect it should have this privilege. This privilege is almost as high-level as DBA. Alter applies only to the table level.

GRANT ALTER ON customer_table TO user1;

REVOKE ALTER ON customer_table FROM user3;

All

The keyword all provides all table and column privileges to users. Using the all keyword grants or revokes any table privileges that the user might have.

GRANT ALL ON customer_table TO user1;

REVOKE ALL ON customer_table FROM user2;

Combinations

You can grant or revoke different combinations of table and column privileges in one command. Place the privileges in any sequence, separated by a comma, after the grant or revoke keyword.

GRANT INSERT, DELETE, UPDATE

ON customer_table TO PUBLIC;

GRANT SELECT, UPDATE (customer_id, balance)

ON customer_table TO user2;

REVOKE INDEX, ALTER ON customer_table FROM user1;

You can also combine table-level and column-level privileges in one statement. Column-level privileges use the specified columns, and table-level privileges use the specified table.

GRANT INSERT, DELETE, SELECT, UPDATE

(customer_id, balance)

ON customer_table TO user2;

REVOKE INDEX, SELECT, ALTER (customer_id, balance)

ON customer_table FROM user3;

Other Keywords

You can use two other keywords in conjunction with the GRANT command. The first is the WITH GRANT OPTION keyword. When combined with the GRANT command, the user receiving the privileges can also grant the same privileges to other users.

In the following example, user1 not only has insert, delete, select and update privileges on customer_table, but he or she can also grant any or all of these privileges to other users.

GRANT INSERT, DELETE, SELECT, UPDATE

ON customer_table TO user1

WITH GRANT OPTION;

If user1 has one or all of the privileges revoked, all the users that user1 granted privileges to will also have the same privileges revoked.

The other keyword used with grant is the AS keyword. The AS keyword allows you to perform a grant as if another user performs the grant. This sets up the situation described previously; if the grantor is revoked, all the users granted by that user are also revoked.

Continuing with the preceding example, user1 was given insert, delete, select, and update privileges on customer_table and the right to grant these privileges. A DBA, the owner of the table, or the user that granted user1 the privileges could then grant as user1 to other users:

GRANT INSERT, DELETE, SELECT, UPDATE

ON customer_table TO user2, user3, user4, user5

AS user1;

Now user1 through user5 have the same privileges. To revoke the privileges on all five users, just revoke user1:

REVOKE ALL ON customer_table FROM user1;

Summary

Security is an important issue with database creators, owners, providers, and users. Not only does security provide a means of keeping the data safe and intact from loss, but it also goes another level by keeping the content of the data safe and secure from misuse or abuse.

You can achieve both levels of database security by using GRANT and REVOKE statements to set privileges at the database, table, and column levels. Setting different types of users also separates users responsible for managing the database, DBAs, and normal users.

You can use stored procedures and triggers to audit how and when data is used or changed and also restrict access to data. You can set up a stored procedure to perform a task on data, and only privileged users can access that stored procedure to perform the task.

Another way of restricting how users access data is through views. You can use a view to force users to perform tasks on a subset of the actual data, rather than access the entire database.

Finally, you can use the operating system procedures to lock users out of the database and the entire system. Client applications should build in specific logon processes to allow only privileged users into the database server.

Read More......

Top Ten onstat Commands

onstat -D

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 20:48:40 -- 72616 s

Dbspaces

address number flags fchunk nchunks flags owner name

c3fa80e8 1 1    1 1 N informix rootdbs
c3fa84b0 2 2001 2 1 N T informix tempdbs
c3fa8518 3 1    3 1 N informix db1
c3fa8580 4 1    4 1 N informix db2

4 active, 2047 maximum

Chunks

address chk/dbs offset page Rd page Wr pathname

c3fa8150 1 1 0 1259 289 /home/informix/ROOTDBS
c3fa8228 2 2 0 11   11  /home/informix/TEMPDBS
c3fa8300 3 3 0 6    0   /home/informix/db1
c3fa83d8 4 4 0 6    0   /home/informix/db2

4 active, 2047 maximum

Monitor the number of page reads and page writes in the "page Rd" and "page Wr" columns. This can be used to determine how even the I/O access to each chunk is. Remember that there may be multiple chunks on a single device.

onstat -F

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 20:51:45 -- 72616 s

Fg Writes LRU Writes Chunk Writes

0         103         311

address flusher state data

c3faa444 0      I     0 = 0X0

states: Exit Idle Chunk Lru

Monitor the types of writes occurring in you system. Foreground (Fg Writes) should be eliminated. LRU Writes and Chunk Writes will vary depending on the type of system you have. An OLTP system should maximize LRU Writes. There will always be some Chunk Writes, but LRU Writes will speed up checkpoint duration. In a DSS system, Chunk Writes should be maximized. Some LRU Writes may still occur in an effort to eliminate foreground writes(Fg Writes).

Also monitor page cleaners (flushers) at checkpoint time. Make sure they are all busy doing Chunk Writes.

 onstat -l

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 20:56:26 -- 72616 s

Physical Logging

Buffer bufused bufsize numpages numwrits pages/io

P-2    0       16       274      22        12.45

phybegin physize phypos phyused %used

10003f   500     433    0        0.00

Logical Logging

Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io

L-2    0       16       2437    113      31        21.6     3.6

address number flags uniqid begin size used %used

c3ecc55c 1    U-B----   7  100233 250  250 100.00
c3ecc578 2    U---C-L   8  10032d 250  106 42.40
c3ecc594 3    F------   0  100427 250  0    0.00
c3ecc5b0 4    F------   0  100521 250  0    0.00
c3ecc5cc 5    U-B----   5  10061b 250  250 100.00
c3ecc5e8 6    U-B----   6  100715 250  250 100.00

 Monitor the physical log buffer usage. Observe the bufsize and the pages/io columns of the first line of the output. If (pages/io) / (bufsize) is roughly 75% then the buffer is being utilized efficiently. If it is less then 75% then the physical log buffer is probably too large. If the ratio is greater than 90% then the physical log buffer is potentially too small.

Monitor the logical log buffer usage the same way as the physical log buffer. However, if unbuffered logging is being used, the flushing of the buffer will depend on the size of transactions, not the utilization of the buffer. This rule may not apply. If most transactions are smaller than a page of the logical log buffer, this ratio may always be low. Just keep the logical log buffers at there defaults.

The physical log file should be monitored near checkpoint time to determine if the percent used is near 75%. A well tuned physical log file will be nearly full at checkpoint time. If the physical log file is not being filled up, then it is wasting disk space.

Also, the logical log files should be monitored to be sure that they are being backed up. By using the sysmasters database, it can be determined which logical log files have been freed. Look at the systrans table for min(tx_loguniq>0) to find the last log file containing an open transaction.

 onstat -m

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 20:57:47 -- 72616 s

Message Log File: /home/informix/online.log

13:09:47 Dataskip is now OFF for all dbspaces
13:09:47 On-Line Mode
13:09:47 Checkpoint Completed: duration was 0 seconds.
14:04:50 Checkpoint Completed: duration was 0 seconds.
14:14:51 Checkpoint Completed: duration was 0 seconds.
14:24:54 Checkpoint Completed: duration was 0 seconds.
14:29:54 Checkpoint Completed: duration was 0 seconds.

Mon Nov 4 11:23:51 1996

11:23:51 Logical Log 7 Complete.
11:27:10 Checkpoint Completed: duration was 0 seconds.

Tue Dec 31 11:16:01 1996

11:16:01 Checkpoint Completed: duration was 0 seconds.
11:21:00 Checkpoint Completed: duration was 0 seconds.
11:26:01 Checkpoint Completed: duration was 0 seconds.
11:36:01 Checkpoint Completed: duration was 0 seconds.

Monitor the online message log file for unusual events which may occur. Also keep tabs on the frequency and duration of checkpoints.

onstat -p

Informix Dynamic Server Version 9.30.UC2E3 -- On-Line -- Up 15:30:00 -- 30704 Kbytes

 

Profile

dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached

14864 46995 6301400 99.76 22142 49296 670458 96.70

 

isamtot open start read write rewrite delete commit rollbk

4931349 8898 511695 1389809 901215 1368 1137 858 0

 

gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs

0 0 0 0 0 0 0

 

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes

0 0 165 104.08 2.76 10 384

 

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans

1457 0 22486669 0 0 0 329 805

 

ixda-RA idx-RA da-RA RA-pgsused lchwaits

44 1983 5561 7583 8

 The profile information has many things that can be monitored.

The read percent cached is important for an OLTP system. The read percent cached should be 95% or higher. This is not always possible because of the applications use of the data. But it is a starting point. Adding more buffers generally will increase the read percent cache.

The write percent cached can also be monitored. But it is much harder to tune. As buffers increase, so should the write percent cache. 85% or higher is a beginning target. However, depending on the application, that percentage may not be achieved.

Lock contention can be monitored by looking at the lokwaits and lockreqs columns. If lokwaits are 1% or higher that of lockreqs, you may have lock contention. Changing the way application use isolation levels and locks will help improve lock contention.

Deadlocks within applications can be detected by the deadlks and dltouts columns. Deadlks is a count of the number of dead locks detected and aborted by online. Dltouts is a count of the number of queries which have had the deadlock timeout time expire. Dead lock timeouts only occur for distributed queries. If deadlocks are occurring, changes will be required in the application.

Read ahead efficiency can be monitored. Add up the values in the ixda-RA, idx-RA, and da-RA columns and compare the sum to RA-pgsused. If the sum of pages read ahead is not nearly equal to the number of pages used, then too many pages are being read ahead. Reduce the RA_PAGES parameter. An additional effect of this will be a reduction of the read percent cache.

Additionally, the RA_THRESHOLD needs to be set close to RA_PAGES or the bufwaits column will increase as the database engine is waiting for the read aheads to complete before it can use the pages.

 onstat -R

# of dirty buffers in each LRU queue

 INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 20:59:53 -- 72616 s

8 buffer LRU queue pairs

# f/m length % of pair total

0 F   25  100.0% 25
1 m   0   0.0%
2 f   25  100.0% 25
3 m   0   0.0%
4 f   25  100.0% 25
5 m   0   0.0%
6 f   25  100.0% 25
7 m   0   0.0%
8 f   25  100.0% 25
9 m   0   0.0%
10 f  25  100.0% 25
11 m  0   0.0%
12 f  25  100.0% 25
13 m  0   0.0%
14 f  25  100.0% 25
15 m  0   0.0%

0 dirty, 200 queued, 200 total, 256 hash buckets, 2048 buffer size

start clean at 60% (of pair total) dirty, or 15 buffs dirty, stop at 50%

Observe the percent of pages in the modified side of each queue at checkpoint time and compare it to the LRU MAX DIRTY parameter. The percentage should always be less than LRU MAX DIRTY. If it is not, then either there are not enough pagecleaners to perform write requests, not enough AIO vps, or KAIO threads to perform physical writes, or the disk controllers or the drives themselves are saturated.

For OLTP systems, reducing LRU MAX DIRTY below the percentage of dirty pages in the LRU queue generally will decrease the duration of checkpoints.

onstat -g ioq

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 21:06:48 -- 72616 Kbytes

 

AIO I/O queues:

q name/id len maxlen totalops dskread dskwrite dskcopy

adt 0 0 0 0 0 0 0

msc 0 0 1 114 0 0 0

aio 0 0 1 1031 14 476 0

pio 0 0 1 27 0 27 0

lio 0 0 1 147 0 147 0

gfd 3 0 1 211 187 24 0

gfd 4 0 152 35991 14627 21364 0

gfd 5 0 140 630 50 580 0

Monitor the len and maxlen columns of the I/O request queues. A maxlen of greater than 25 or a len of 10 which is consistent indicates that the requests are not being serviced fast enough. Adding more VPs will help if the disks or controllers are not already saturated.

onstat -g lsc

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 123 days 01:39:41 -- 72616 s

Light Scan Info

descriptor address next_lpage next_ppage ppage_left bufcnt look_aside

0 c41edcd0 87 500223 490 1 N

1 c41efe68 85 6000cc 497 1 Y

1 c41f07b8 83 7000cc 497 1 Y

Use onstat -g lsc to determine if light scans are being utilized. If the size of the table is larger than the size of the buffers and the isolation level is set to dirty read, or committed read with a shared lock on the table, then light scans should happen. Light scans will significantly speed up large table scans. The ppage_left column will display the number of pages still to scan for a given fragment of a table.

Onstat -g ntd

INFORMIX-OnLine Version 7.12.UC2 -- On-Line -- Up 122 days 21:01:14 -- 72616

global network information:

#netscb connects read write q-free q-limits q-exceed alloc/max

5/ 8 50 2750 12000 1/ 1 279/ 10 0/ 0 1/ 1

 

Client Type Calls Accepted Rejected Read Write

sqlexec yes 50 0 2700 12000

srvinfx yes 0 0 0 0

onspace yes 0 0 0 0

onlog yes 0 0 0 0

onparam yes 0 0 0 0

oncheck yes 0 0 0 0

onload yes 0 0 0 0

onunload yes 0 0 0 0

onmonitor yes 0 0 0 0

dr_accept yes 0 0 0 0

cdraccept no 0 0 0 0

ontape yes 0 0 0 0

srvstat yes 0 0 0 0

asfecho yes 0 0 0 0

listener yes 0 0 50 0

crsamexec yes 0 0 0 0

safe yes 0 0 0 0

onutil yes 0 0 0 0

Totals 50 0 2750 12000

Monitor the number of accepted vs. rejected connections. If there are a large number of rejections then either the user table has overflowed (onstat -p ovuserthreads) or the network is timing out on the connection.

Onstat -g ppf

INFORMIX-OnLine Version 7.13.UC2 -- On-Line -- Up 3 days 15:51:58 -- 213816 Kbytes

 

Partition profiles

partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio

0x100001 0 0 0 0 0 0 0 0 0 0 0 0

0x100002 195 0 0 0 49 0 0 0 152 0 3 95

0x100003 366 0 0 0 170 0 0 0 432 0 0 94

0x100004 77 0 0 0 34 0 0 0 84 0 0 89

0x100005 12 0 0 0 4 0 0 0 12 0 0 75

0x100006 17 0 0 0 7 0 0 0 20 0 0 65

0x100007 17 0 0 0 7 0 0 0 17 0 0 83

0x100008 45 0 0 0 21 0 0 0 48 0 0 82

0x100009 69 0 0 0 31 0 0 0 97 0 3 93

0x10000d 0 0 0 0 0 0 0 0 2 0 0 50

0x100010 2 0 0 0 0 0 0 0 2 0 0 50

0x100012 22 0 0 0 9 0 0 0 28 0 0 65

0x100013 26 0 0 0 11 0 0 0 30 0 0 87

0x100014 3 0 0 0 1 0 0 0 9 0 0 56

0x100015 60 0 0 0 24 0 0 0 72 0 0 88

0x100016 3 0 0 0 3 0 0 0 3 0 0 67

0x100017 0 0 0 0 3 0 0 0 3 0 0 67

0x100018 0 0 0 0 0 0 0 0 2 0 0 50

0x10001a 0 0 0 0 0 0 0 0 2 0 0 50

Monitor the number of read and write operations which are occurring on open fragments of tables using isrd, iswrt, isrwt and sdel. Since only open tables are listed, sampling onstat -g ppf over time provides good indication of which tables are most frequently used. Determine if sequential scans are being used to read data from the table using seqsc. Compare fragments from the same table to determine If I/O is balanced across the fragments of the table.

  

Read More......

Setup rootdbs Lean & Clean

When Informix is installed with default options, all of the elements of the database are placed in the initial dbspace, which is called the rootdbs. The Informix installation process generates the minimum configuration that works and does not attempt to optimize any database elements.

One problem with this default installation is that Informix places its logical log files and physical log files in the rootdbs. And one of the most common mistakes that rookie Informix DBAs make is to leave the logical and physical log files in the rootdbs—or even create entire databases there. For performance and maintainability reasons, those log files should really occupy their own dbspaces. This 10-Minute Solution shows you how to avoid this problem.

The Problem

Informix's default installation places the logical and physical log files in the rootdbs. This can cause severe performance and maintenance issues for DBAs if not dealt with early on.


The Solution

There are several things you need to do in order to keep your rootdbs lean and clean:

1. Confirm the contents of rootdbs.
   
2. Choose the right disk spaces for the log files.
   
3. Place the logical and physical logs in their own dbspaces.
   
4. Decide where to place user databases.
   

Keeping Your Informix rootdbs Lean and Clean

Confirming the Contents of rootdbs
When you see a rootdbs larger than 50–75MB, you can be pretty sure that the logical and physical log files have been left in the rootdbs. The only database objects that need to be in the rootdbs are the system tables and other internal data structures that are managed by IDS.

You can confirm the contents of your rootdbs by running the following command:

oncheck –pe  > tempfile
					

The only database objects that you should find in chunk 1 (the rootdbs) are the following:

• Objects in the sysmaster database
  
• Objects in the sysutils database
  
• TABLESPACE TABLESPACE
  
• ROOT DBspace RESERVED Pages
  
• CHUNK FREE LIST PAGE
  
• DATABASE TABLESPACE
  
• FREE space (chunk space that is not currently allocated to tables)
  

If you have found physical or logical log files in the rootdbs or if you have found other database objects, you should consider moving them out of the rootdbs to improve performance and maintainability.

Choosing the Right Disk Spaces for Log Files
One factor affecting performance is access speed. The high write loads that are placed on the logical and physical log files means there is an excessive disk head movement as the head tries to read from many non-adjacent areas of the disk. To avoid this disk thrashing, you should place the logical and physical log files only on your fastest-writing disk spaces.

It's best not to put them in RAID 5 spaces because write speeds are slower on RAID 5 systems. Instead, place them either on individual disks or mirrored disks.

Keeping Your Informix rootdbs Lean and Clean (cont.)

Placing Logical and Physical Logs
in Their Own dbspaces
Informix installs with a small number of small logical logs in the rootdbs. As the DBA, you most often need to add log files in order to avoid problems with long transactions. When a transaction runs out of logical log space, it is rolled back. If there is not enough log space available for the rollback, the database may have to be restored from an archive. This is often known as the "Informix death penalty."

To alleviate this problem, place your logical and physical logs in their own dbspaces. A good rule of thumb for creating logical logs is to create enough of them to equal about 10 to 20 percent of the size of the total database.

Here's how:

1. Change the LOGSMAX parameter in the onconfig file from its default to some randomly large number. I usually use anything from 50 to 500. This parameter only changes the maximum allowable number of log files; it does not actually create the log files.
   
2. Change the TAPEDEV parameter in the onconfig file to /dev/null. Remember to change it back when you're finished. An easy way to do this is to duplicate the TAPEDEV line in the onconfig file and change the device to /dev/null on UNIX systems or to NUL on Windows NT systems. Comment out the one you don't need by placing a "#" in front of the unwanted line.
   
3. Note the number of the last original logfile by checking its "number" column in the output of "onstat –l".
   
4. Create separate dbspaces for the logical and physical log files. Give the spaces a descriptive name so that you can identify their purposes from their name. I use "logspace" and "physlogspace" for the names in my systems. Remember that a dbspace cannot exceed 2GB. If you need bigger logspaces, enlarge them by adding additional 2GB storage areas to the existing logspace. I generally create a physical logspace equal to about two to three times the size of one logical logfile. You can modify the amount of the physical logs later as you tune the system.
   
5. Take the database completely offline and restart it, so that the changes you made to the onconfig file take effect. It will come back up in quiescent mode. Leave it there. Do not take it to online mode.
   
6. Add the new logical log files. For each one, use the following command:
   

   onparams –a –d <logspace name> -s <size in K> -y
   							

7. Make the log files active by executing a "fake archive" to /dev/null:
   

   ontape –s
   							

   This archive is almost instantaneous.
   

8. Use the following command repeatedly until the current logfile (indicated by a "C" in the flags field of the "onstat –l" output) is one of the new log files rather than one of the original ones. You can differentiate the old from the new log files by noting the "number" column of the "onstat –l" command:
   

   onmode –l
   							

9. Delete the old log files by using the following command repeatedly, changing the "logid" value until it increments up to the last logfile number that you noted in Step 3:
   

   onparams –d –l <logid> -y
   							

10. Move the physical log into its own dbspace by using the following command:
    

    onparams –p –s <size> -d <physdbspace name> -y
    							

11. Do another fake archive to /dev/null in order to recover the space freed up from the deletion of the old log files.
    

    
     

12. Take the database offline again and bring it back into online mode.
    

If you automate Steps 6 and 9 by creating a script file with the appropriate onparams commands, you'll make the process of properly setting up the log files a simple, fool-resistant task.

Note that I didn't say "foolproof." Nothing is foolproof because fools are crafty and can outwit most normal precautions!

Keeping Your Informix rootdbs Lean and Clean (cont.)

Moving User Databases Outside rootdbs
Unless you specifically requests otherwise, the default Informix installation will create the user databases in the rootdbs. Do not do this! If you do, you will never be able to regain the space if you drop them, because you cannot drop the rootdbs or reduce its size after it has been created.

If the databases are small and not frequently used, you can probably afford to leave them in place. If they are junk databases, simply drop them by using this simple SQL command:

drop database <dbname>
					

 

If your databases are worth keeping, however, you can recreate them in their own dbspaces by using the dbexport and dbimport commands.

First, export the database to ASCII files:

dbexport <dbname> -ss
					

 

This command creates a directory called <dbname>.exp and places the ASCII export files for each table in the directory.

To reimport these databases into the correct database, use the dbimport command:

dbimport <dbname> -d <spacename> -l buffered
					

 

This assumes that you are using buffered logs. If not, check the manual for the correct syntax for the import.

When you are creating new databases, use the following syntax to avoid putting the database in the rootdbs:

create database <dbname> in <spacename> with buffered log
					

As I stated at the outset of this article, cramming everything into the rootdbs is a sure sign that the system has not been designed for maximum performance. As you fill up the rootdbs with log files and other extraneous databases, you run the chance of either filling up the rootdbs or deteriorating disk performance.

Keeping a clean rootdbs makes it easier to tune such things as physical log size as you learn more about the performance needs of any particular database. Keeping the rootdbs clean and lean is a sign of a careful, attentive DBA.

 

Summary

1)create dbspaces : onspaces –c –d logdbs –p <path> -o –s

2)edit onconfig : setting PHYSDBS to logdbs

3)down to quiescent mode : onmode –u

4)run onmonitor to used new dbspace: onmonitor > parameter > physical log

5)drop logical log that used rootdbs : onparams –d –l <log number>

6)add new logical log to new dbspaces: onparams –a –d logdbs

7)run onmode –l until it used the new dbspaces

8)make level 0 archieve

Read More......

Informix Utilities

INFORMIX-OnLine comes with a set of powerful command line utilities that enable you to monitor, tune, and configure your database server. The command line utilities and the order in which we will discuss them is as follows:

• ONSTAT - shows shared memory and server statistics
  
• ONCHECK - checks and repairs disk space
  
• ONMODE - changes Servers's operating mode
  
• ONLOG - logical log debugging tool
  
• ONINIT - initialize and start up the database server
  
• ONSPACES - configure dbspaces and chunks
  
• ONPARAMS - configure logs
  
• ONTAPE - backup and restore utility
  
• ONLOAD - loads databases and tables
  
• ONUNLOAD - unloads databases and table
  
  

ONSTAT - shows server statistics

ONSTAT is the command line utility that gets the most usage. It reads IDS's shared memory structures and provides lots of useful information about the state of your server. It does not place any locks on shared memory structure and uses very little overhead, so you can use it at any time. The information is current at the time the command is issued, and the data can change as you are using the command. There are more options for ONSTAT than any other Informix utility. Many of the options are debugging parameters that are not well documented and don't make sense to the average DBA. There are also many very useful options that help you manage your IDS server.

ONCHECK - Check and display information about IDSs's disk space

ONCHECK is the tool to check and display information about your dbspaces, blobspaces, chunks, tables, indexes, and disk pages. The purpose of this utility is to insure that your database server disk space has no inconsistencies. I like to think of this as the database version of the UNIX utility 'fsck' which checks file systems, or the DOS utility 'chkdsk' which checks DOS disk space. ONCHECK will place locks on all tables and databases that it needs to access. In some cases it will place an exclusive lock on a database or table and prevent other users from accessing the data. You need to be careful when you run ONCHECK to make sure it will not disrupt your users' work. It is a good idea to run ONCHECK when the IDS Server is in quiescent mode so it does not conflict with other users.

ONMODE - change IDS operating mode

The ONMODE utility has several key functions: it changes the operating mode of IDS, shuts IDS down, allows you to change some configuration parameters on the fly, and provides a means to kill user database connections. Figure 25 provides the complete syntax to the ONMODE command.

Figure 25: ONMODE syntax

onmode -abcDdFklMmnpQRrSsuyZz

-a <kbytes> Increase shared memory segment size

-b <version> Revert disk structures to an older version

-c Perform a checkpoint

-D <max PDQ priority allowed> Set max PDQ

-d {standard|{primary|secondary <servername>}} set Data Replication server type

-F Free unused memory segments

-k Shutdown completely

-l Force switch to next logical log

-M <decision support memory in kbytes> Set size of Decision Support Memory

-m Go to multi-user on-line mode from quiescent mode

-n Set shared memory buffer cache to non-resident

-O Override dbspace down blocking a checkpoint

-p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio,

shm, soc, or tli

-Q <max # decision support queries> Set max number of Decision Support queries

-R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file

-r Set shared memory buffer cache to resident

-S <max # decision support scans> Set max number of Decision Support Scans

-s Shutdown to single user (Graceful shutdown)

-u Shutdown and kill all attached sessions (Immediate Shutdown)

-y Do not require confirmation mode changes

-Z <address> heuristically complete specified transaction

-z <sid> Kill specified database session id

Shutting down the database server

One of the most common uses of the ONMODE utility is to shutdown the database server. To immediately shutdown the server from any mode, type:

onmode -ky

The '-k' option takes the database server off-line and the 'y' avoids the prompt to confirm your action. This immediately take the server off-line, disconnecting all users. Any user in the middle of a transaction will have their transaction rolled back to the state before they started their transaction. Any work the user was doing will be lost. You must be the user 'root' or 'informix' to perform this function.

Figure 26 shows the error message you will get if you are not logged in as 'informix' or 'root' to shutdown IDS. It also shows the message you and your users will receive when IDS has been shutdown and you try to access the database server.

Figure 26: Shutting down IDS

lester@merlin >onmode -ky

Must be a DBSA to run this program

lester@merlin >su informix

Password:

lester@merlin >onmode -ky

lester@merlin >onstat -

shared memory not initialized for INFORMIXSERVER 'train1'

When all the electrical power is about to fail, or the computer is shutting down for whatever reason, you don't have time to ask all users to log off the database server. And if you do not shutdown IDS, it will crash in an inconsistent state, with data in memory buffers that is not been written to disk, and users in the middle of transactions. When IDS is later restarted in will start a recovery mode to clean up from the crash, but this can take time and there may be problems.

One useful method of invoking this command is to put it in the UNIX shutdown script for your machine. This way when the computer is stopped it will automatically stop IDS. Check with your UNIX System Administrator on the location of the script. I like to add a call to a separate shell script that uses ONMODE to shutdown the server and ONINIT to start it up, based on a parameter passed to the script. See Figure 27 for an example script that starts and stops multiple IDS Servers. A good way to test such a startup script is to execute it as root, using the Bourne Shell with none of the Informix environment variables set.

Figure 27: IDS startup and shutdown script

#############################################################################

# Module: %W% Date: %D%

# Author: Lester B. Knutsen email: lester@access.digex.net

# Advanced DataTools Corporation

# Discription: Informix IDS startup/Shutodwn script

# This script is used to start and stop 3 IDS Servers

# used for training named: train1, train2, train3

#############################################################################

# Set Global environment variables

#############################################################################

## Set the location of Informix Programs

INFORMIXDIR=/u3/informix7

export INFORMIXDIR

## Add the Informix Programs to your PATH

PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin

export PATH

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train1

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train1

export ONCONFIG

state=$1

case $state in

start)

oninit;

echo "Informix Server: $INFORMIXSERVER Started";;

stop)

onmode -ky;

echo "Informix Server: $INFORMIXSERVER Shutdown";;

*)

echo "usage: ifx.rc start|stop";;

esac

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train2

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train2

export ONCONFIG

state=$1

case $state in

start)

oninit;

echo "Informix Server: $INFORMIXSERVER Started";;

stop)

onmode -ky;

echo "Informix Server: $INFORMIXSERVER Shutdown";;

*)

echo "usage: ifx.rc start|stop";;

esac

#############################################################################

# Process and shutdown server

#############################################################################

## Set the Database Server

INFORMIXSERVER=train3

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train3

export ONCONFIG

state=$1

case $state in

start)

oninit;

echo "Informix Server: $INFORMIXSERVER Started";;

stop)

onmode -ky;

echo "Informix Server: $INFORMIXSERVER Shutdown";;

*)

echo "usage: ifx.rc start|stop";;

esac

Changing IDS modes

In addition to the 'onmode -k' option to shutdown, ONMODE has three other options to change the mode of IDS. The '-k' option completely shut down the database server and takes it off-line. There are two options that take the database server to quiescent mode. Quiescent mode is like a maintenance mode or single-user mode where you can access IDS with the utilities but users cannot connect.

The command to gracefully take IDS to quiescent mode is:

onmode -s

The command to immediately take IDS to quiescent mode is:

onmode -u

The difference between these is that the '-s' option will wait until all users have disconnected before changing modes, and the '-u' option will change modes immediately and kill all connected users.

To return to on-line mode rrom quiescent mode so users can once again access the database server, the command is:

onmode -m

Forcing a checkpoint

A checkpoint is one of the key events when IDS syncs shared memory with what is on disk. Several activities depend on the last completed checkpoint. An archive takes its start date and time from the last checkpoint. You cannot delete a logical log that contains the last checkpoint. To force IDS to perform a checkpoint, use the following onmode command and option:

onmode -c

Forcing a switch in the current logical log

Another option to ONMODE allows you to change the current logical log to the next logical log in sequence. This is required if you are going to backup the current logical log or to drop the current logical log. The command and option to change the current logical log is:

onmode -l

Free unused virtual memory segments

As IDS runs, it will add additional virtual shared memory segments as needed. Since this operation has some overhead, IDS does not release unused memory segments, but saves them for future reuse. The 'onstat -g seq' command discussed earlier in the chapter shows you the current virtual memory segments. The command to force IDS to reorganize its virtual memory segments and free unused segments is:

onmode -F

This operation requires some overhead and will freeze all user processing while IDS reorganizes and frees this segment. Because of the overhead of free memory and then re-adding it later, this operation should only be done when required. Monitor your virtual memory segments with the command 'onstat -g seg'. When you notice an increase in the virtual memory segments, and you see that these are no longer being used, then it may be useful to free them with this command. A common occurrence of this is after running large weekly or month-end batch jobs and reports. These type of jobs will often require extra memory that will be used until the next cycle of processing. This is a good opportunity to use this command. Do not repeatedly run this command at short intervals to free memory. The overhead of freeing memory and then re-acquiring it will slow things down.

Killing users' database processes

ONMODE provides an option to kill and abort an individual user's database process. This option is aware of a user's database transaction and will rollback any work that was not committed. Operating system commands to kill a user's process (e.g. the UNIX kill -9 command) are not aware of a user's database connection and may not cleanly rollback their work. This can lead to corruption of tables or indexes. The correct procedure to kill a user's database process is:

1. Identify the user's session id using the ONSTAT command with one of the following three options:

onstat -u

onstat -g sql

onstat -g ses

2. Use the following omode command to terminate the user's session:

onmode -z session_id

Figure 28 shows an example of identifying the session id for the user 'lester' using 'onstat -u' and killing the session with 'onmode -z' The session id is 190.

Figure 28: Terminating a user's session

lester@merlin >onstat -u

INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 28 days 11:52:49 -- 10656 Kbytes

Userthreads

address flags sessid user tty wait tout locks nreads nwrites

a2d0018 ---P--D 1 informix - 0 0 0 114 486

a2d0458 ---P--F 0 informix - 0 0 0 0 5657

a2d0898 ---P--B 8 informix - 0 0 0 2 0

a2d1558 ---P--D 12 informix - 0 0 0 0 2

a2d1998 Y--P--- 190 lester 0 a3d1d50 0 1 18 0

5 active, 128 total, 17 maximum concurrent

lester@merlin >onmode -z 190

lester@merlin onstat -u

INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 28 days 11:53:00 -- 10656 Kbytes

Userthreads

address flags sessid user tty wait tout locks nreads nwrites

a2d0018 ---P--D 1 informix - 0 0 0 114 486

a2d0458 ---P--F 0 informix - 0 0 0 0 5657

a2d0898 ---P--B 8 informix - 0 0 0 2 0

a2d1558 ---P--D 12 informix - 0 0 0 0 2

4 active, 128 total, 17 maximum concurrent

When you terminate a user's session, their processing may not stop immediately. If they are performing a large query, data may be buffered up on the user's program and they may continue to receive data. Once the buffer is empty, the user will receive an error message indicating the database connection was lost.

Another delay will occur if the user was in the middle of a large update or load in a transaction. IDS will need to rollback the transaction. The general rule that I use is that if the user was in a transaction for 30 minutes loading data, it will take about 30 minutes to rollback their work. IDS must delete all the records it has inserted. You must let the rollback complete.

Using ONMODE for configuration changes

ONMODE has several options that allow you to change your configuration while the database server is up and running. This saves changing the ONCONFIG file, shutting down the server, and then restarting it with the new configuration. However, changes made using ONMODE are not written to the ONCONFIG file and will be lost when IDS is shutdown and restarted.

The following options to ONMODE allow changes:

-a <kbytes> Increase shared memory virtual segment size

-b <version> Revert OnLine disk structures to an older version of OnLine (e.g 5, 6)

-d {standard|{primary|secondary <servername>}} set Data Replication server type

-n Set shared memory buffer cache to non-resident

-O Override dbspace down blocking a checkpoint

-p <+-#> <class> Start up or remove virtual processors of class cpu, aio, lio, pio,

shm, soc, or tli

-R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file

-r Set shared memory buffer cache to resident

Decision support Configuration changes

-D <max PDQ priority allowed> Set max PDQ

-M <decision support memory in kbytes> Set size of Decision Support Memory

-Q <max # decision support queries> Set max number of Decision Support queries

-S <max # decision support scans> Set max number of Decision Support Scans

ONLOG - debug the logical log

The ONLOG utility allows you to debug transactions using the logical logs. This utility should be used with care as it can display lots of information. The best use of this utility is to research why a user's transaction failed. Figure 29 contains the syntax for this command.

Note: Running ONLOG on the current logical log file (the default) will lock the log file and stop all user processing.

Figure 29: ONLOG syntax

onlog [-l] [-q] [-b] [-d <tape device>] [-n <log file number>] [-u <user name>]

[-t <TBLspace number>] [-x <transaction number>]

-l Display maximum information about each log record including hex dump

-q Do not display program header

-b Display information about logged BLOB pages (-d option only)

-d Read from tape device

-n Display the specified log(s)

-u Display the specified user(s)

-t Display the specified TBLspace(s)

-x Display the specified transaction(s)

ONINIT - initialize and start-up functions

The ONINIT utility starts IDS and without any other options brings it into on-line mode so users can connect and go to work. This is one of the key commands along with 'onmode -ky' to start and stop your database server. See Figure 27 for a script that uses these commands to automatically start or stop IDS.

However, ONINIT is also one of the most dangerous commands. With a single option (-i) it will initialize you rootdbs, wiping out anything that was there and all your hard work. There may be times you will want to do this but be very careful and make sure all your environment variables are set correctly.

Starting IDS

The command to start IDS is very simple. Just type 'oninit', and it will use four environment variables to identify the database server to start. The environment variables are:

INFORMIXDIR - Points to the directory where the Informix products are installed and is used by IDS as a base directory to locate other files it needs.

PATH - This is used by your operating system to search for executables and must include the directory located in $INFORMIXDIR/bin.

INFORMIXSERVER - This is the name of the IDS server you wish to start. This name is also located in your ONCONFIG file.

ONCONFIG - This is the name of the configuration file IDS will use to start the database server. It is located in $INFORMIXDIR/etc.

Once these are set and you are logged in as the user 'root' or 'informix', type 'oninit' to start the server. Figure 27 was an example of a script that will start several IDS servers. Normally you want your IDS server to start automatically every time you boot your computer. This script can be called from one of the UNIX startup scripts like '/etc/rc.local' to perform this function. Check with your operating system administrator to find the name of the UNIX startup script that you will need to call this script from.

Figure 30 shows all the options to ONINIT.

Figure 30: ONINIT syntax

oninit [-I] [-p] [-s] [-y] [-V] [-v]

-I Startup and initialize rootdbs. This will destroy any existing data in your rootdbs

-p Startup and do not delete temp tables during shared memory initialization

-s Startup and stay in quiescent mode

-V Display version

-v Startup in verbose mode. Many additional messages will display that are helpful in debugging problems.

Initializing the root dbspace

The '-I' option of ONINIT is very powerful and dangerous. It will startup IDS and initialize your rootdbs. This process is like formatting a dbspace and will destroy all data that is there. However, this option is very handy when you know what you are doing and want to initialize your rootdbs. When I need to configure several database servers it is easier to write a script to do it rather then type all the commands and edit all the options by hand. Figure 35, shows an example script that does this, and uses the next two utilities we will talk about to initialize a rootdbs, set up several dbspaces, and move the logical logs to a separate dbspace.

Verbose Option

Another helpful option is the lower case '-v' for verbose. This displays extra messages as IDS goes through the different stages on initialization and is very helpful when debugging an installation. Figure 31 shows the output from this option on my training system.

Figure 31: Verbose startup of IDS with the '-v' option

lester@merlin >oninit -v

Reading configuration file '/u3/informix7/etc/onconfig.train1'...succeeded

Creating /etc/.infxdirs ... succeeded

Creating infos file "/u3/informix7/etc/.infos.train1" ... "/u3/informix7/etc/.conf.train1" ... succeeded

Writing to infos file ... succeeded

Checking config parameters...succeeded

Allocating and attaching to shared memory...succeeded

Creating resident pool 2160 kbytes...succeeded

Creating buffer pool 402 kbytes...succeeded

Initializing rhead structure...succeeded

Initializing ASF ...succeeded

Initializing Dictionary Cache and Stored Procedure Cache...succeeded

Onlining 0 additional cpu vps...succeeded

Onlining 2 IO vps...succeeded

Forking main_loop thread...succeeded

Initialzing DR structures...succeeded

Forking 1 'ipcshm' listener threads...succeeded

Forking 1 'tlitcp' listener threads...succeeded

Starting tracing...succeeded

Initializing 1 flushers...succeeded

Initializing log/checkpoint information...succeeded

Opening primary chunks...succeeded

Opening mirror chunks...succeeded

Initializing dbspaces...succeeded

Validating chunks...succeeded

Forking btree cleaner...succeeded

lester@merlin >Initializing DBSPACETEMP list

Checking database partition index...succeeded

Checking location of physical log...succeeded

Initializing dataskip structure...succeeded

Checking for temporary tables to drop

Forking onmode_mon thread...succeeded

Verbose output complete: mode = 5

lester@merlin >onstat -

INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 00:00:47 -- 10656 Kbytes

Figure 32 shows the output to the IDS message log of a successful startup. There are two key messages you need to look for in the message log when IDS starts. One is the message 'Physical Recovery Completed: 0 Pages Restored', and the other is 'Logical Recovery Complete'. The zeros for 'Pages Restored' and 'Rolled Back' mean that everything was shutdown cleanly and restarted cleanly with no loss of users' work.

Figure 32: Startup messages in the IDS Message Log

Sat Aug 9 23:52:16 1997

23:52:16 Event alarms enabled. ALARMPROG = '/u3/informix7/log_full.sh'

23:52:17 DR: DRAUTO is 0 (Off)

23:52:18 INFORMIX-OnLine Initialized -- Shared Memory Initialized.

23:52:18 Physical Recovery Started.

23:52:18 Physical Recovery Complete: 0 Pages Restored.

23:52:18 Logical Recovery Started.

23:52:21 Logical Recovery Complete.

0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks

23:52:22 Dataskip is now OFF for all dbspaces

23:52:22 On-Line Mode

23:52:22 Checkpoint Completed: duration was 0 seconds.

ONSPACES - Adding, deleting, and changing mirroring of dbspaces

This utility allows you to add, drop and change the mirroring of dbspaces. It is the equivalent of the menu options in ONMONITOR. If you seldom change your dbspace configuration it is easier to use the menus in ONMONITOR. The ONSPACES utility is very useful if you need to create scripts to change your dbspace configuration. Figure 33 has the syntax for this utility. Figure 35 is an example of a script that uses this utility to configure a database server from scratch.

Figure 33: ONSPACES syntax

onspaces { -a spacename -p pathname -o offset -s size [-m path offset] |

-c {-d DBspace [-t] | -b BLOBspc -g pagesize} -p pathname -o offset -s size

[-m pathoffset]

-d spacename [-p pathname -o offset] [-y] |

-f [y] off [DBspace-list] | on [DBspace-list] |

-m spacename {-p pathname -o offset -m path offset [-y] |

-f filename} |

-r spacename [-y] |

-s spacename -p pathname -o offset {-O | -D} [-y] }

-a Add a chunk to an existing DBspace or BLOBspace

-c Create a new DBspace or BLOBspace

-d Drop a DBspace, BLOBspace or chunk

-f Change dataskip default for specified DBspaces

-m Add mirroring to an existing DBspace or BLOBspace

-r Turn mirroring off for a DBspace or BLOBspace

-s Change the status of a chunk

Note: You can only drop a dbspace if it is completely empty.

ONPARAMS - Change logical and physical log configuration

This utility allows you to add logical logs, drop logical logs, and change the location of the physical log. Figure 34 shows the syntax for this command. This utility is handy because you can do some things with it that cannot be done with ONMONITOR. It allows you to add logical logs of different sizes and locations. One common use of this is after you have set up your server, you will often want to move your logs out of the rootdbs into their own dbspaces. Figure 35 contains an example of a script using this command to move the logical logs to their own dbspace and the physical log to its own dbspace.

Figure 34: ONPARAMS syntax

onparams { -a -d DBspace [-s size] | -d -l logid [-y] |-p -s size [-d DBspace] [-y] }

-a Add a logical log

-d Drop a logical log

-p Change physical log size and location

-y Answer YES to all questions

In order to drop a logical log, the log must be backed up and cannot contain the current checkpoint or current logical log.

Figure 35: Script to initialize a database server, add dbspaces, and add logs

#############################################################################

# Module: %W% Date: %D%

# Author: Lester B. Knutsen

# Advanced DataTools Corporation

# Discription: Script to Creat a training environment Informix IDS

# database server

#############################################################################

#############################################################################

# Set Global environment variables

#############################################################################

## Set the location of Informix Programs

INFORMIXDIR=/u3/informix7

export INFORMIXDIR

## Add the Informix Programs to your PATH

PATH=$INFORMIXDIR/bin:$PATH:/usr/ccs/bin

export PATH

## Set the Database Server

INFORMIXSERVER=train2

export INFORMIXSERVER

## Set the Informix Configuration File

ONCONFIG=onconfig.train2

export ONCONFIG

#############################################################################

# Check that this is the correct ONCONFIG and INFORMIXSERVER

#############################################################################

set `grep "^DBSERVERNAME" $INFORMIXDIR/etc/$ONCONFIG`

if [ "$2" != "$INFORMIXSERVER" ]

then

echo "Invalid INFORMIXSERVER: $INFORMIXSERVER"

exit

fi

echo "Creating and Initializing INFORMIXSERVER: $INFORMIXSERVER"

echo "Press RETURN to continue"

read ans

#############################################################################

# Create the disk devices - this training server uses cooked files

# but you could replace these command with the commands to use raw files.

#############################################################################

touch /u3/dev/rootdbs2

touch /u3/dev/logdbs2

touch /u3/dev/rootdbsM2

touch /u3/dev/data2dbs2

touch /u3/dev/tempdbs2

# Set owner to informix - group informix

chown informix:informix /u3/dev/rootdbs2

chown informix:informix /u3/dev/logdbs2

chown informix:informix /u3/dev/rootdbsM2

chown informix:informix /u3/dev/data2dbs2

chown informix:informix /u3/dev/tempdbs2

# Set permissions to read/write owner and group only

chmod 660 /u3/dev/rootdbs2

chmod 660 /u3/dev/logdbs2

chmod 660 /u3/dev/rootdbsM2

chmod 660 /u3/dev/data2dbs2

chmod 660 /u3/dev/tempdbs2

#############################################################################

# Initialize the rootdbs - after this anything that was there is wipped out

#############################################################################

oninit -i

## must sleep long enough for the sysmaster database to be created or the

## next step will fail.

sleep 200

FuGEN ad# Display the log

onstat -m

## now shutdown to single user mode

onmode -sy

# Display status

onstat -

#############################################################################

# Creat the additional Dbspaces

#############################################################################

echo "Creating logdbs..."

## Create dbspace for logical logs

onspaces -c -d logdbs -p /u3/dev/logdbs2 -o 0 -s 25000

echo "Creating datadbs..."

## Create dbspace for data

onspaces -c -d datadbs -p /u3/dev/data2dbs2 -o 0 -s 50000

echo "Creating tempdbs..."

## Create dbspace from temp tables

onspaces -c -d tempdbs -t -p /u3/dev/tempdbs2 -o 0 -s 10000

#############################################################################

# Create additional logical logs in logsdbs

#############################################################################

echo "Creating additional Logical Logs"

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

onparams -a -d logdbs -s 4000

echo "Creating archive to activate new Logical Logs"

ontape -s

#############################################################################

# Show message log and status

#############################################################################

onstat -m

echo "IDS Configuration complete"

#############################################################################

ONTAPE - the IDS backup and restore utility

ONTAPE is the basic utility to backup and restore the whole IDS server. The backup may be performed while the system is running and while users are accessing and updating data. In addition to your basic backup and restore ONTAPE performs a few other functions:

Backups the logical logs.

Provides a utility to change the logging mode of a database.

Performs a restore to start Data Replication.

IDS backup strategies and procedures are very important and merit a whole chapter. This is just a quick overview of ONTAPE's syntax and a few options. Figure 36 contains the syntax for ONTAPE.

Figure 36: ONTAPE syntax

ontape { -a | -c | -l | -p | -r [-D DBspace_list] | -s [-L archive_level]

[-A database_list] [-B database_list] [-N database_list] [-U database_list] }

-a Automatic backup of logical logs

-c Continuous backup of logical logs

-l Logical restore

-p Physical restore for Data Replication (HDR)

-r Full restore DBspaces/BLOBspaces as listed

-s Archive full system

-A Set the following database(s) to ansi logging

-B Set the following database(s) to buffered logging

-N Set the following database(s) to no logging

-U Set the following database(s) to unbuffered logging

Limitations of ONTAPE

The basic ONTAPE restore option restores the whole database server. You cannot restore just one table or database. Use ONUNLOAD and ONLOAD to perform database and table level backups.

ONTAPE can only restore to a the same dbspace configuration. The disk layout must match exactly the disk layout when the backup was made.

The ONTAPE backup is binary and can only be restored to a computer which is binary compatible and using the same version of Informix.

Backing up the IDS server

ONTAPE provides a way for you to backup the whole database server while it is running. ONTAPE will keep track of all changes made during its backup, and during a restore rollback any incompletely backed-up changes. The command to start a backup is:

ontape -s

ONTAPE uses the parameters in the ONCONFIG file to determine the tape device, block size and tape size. These parameters are:

TAPEDEV /dev/tapedev # Tape device path

TAPEBLK 16 # Tape block size (Kbytes)

TAPESIZE 1024000 # Maximum amount of data to put on tape (Kbytes)

Changing TAPEDEV to /dev/null and performing a backup will reset IDS's internal parameters without performing an actual backup.

Using ONTAPE requires a dedicated terminal and tape drive during backups only. It will also require an operator to monitor backups and change tapes as needed. For the backups to be used in a restore, the tapes must be labeled carefully and coordinated with Logical Log backup. Figure 37 contains an example of an IDS backup using ONTAPE.

Figure 37: ONTAPE backup

informix@merlin >ontape -s

Please enter the level of archive to be performed (0, 1, or 2) 0

Please mount tape 1 on /dev/rmt/0 and press Return to continue ...

10 percent done.

20 percent done.

30 percent done.

Tape is full ...

Please label this tape as number 1 in the arc tape sequence.

This tape contains the following logical logs:

9

Please mount tape 2 on /dev/rmt/0 and press Return to continue ...

Backing up to disk

Informix does not officially support backing up to disk with ONTAPE but it can be done. Figure 38 contains an example of a shell script that could be used to backup IDS to a disk file. This could be run by the UNIX Cron facility to automatically backup your server at a set time. However, the backup must be small enough to fit on disk.

Figure 38: Shell script to backup IDS to disk

####################################################################

# Shell script to backup Informix IDS to disk

####################################################################

## Set Informix environment variables

## change these to match your configuration

INFORMIXDIR=/usr/informix7.1

export INFORMIXDIR

PATH=$INFORMIXDIR/bin:$PATH

export PATH

ONCONFIG=onconfig

export ONCONFIG

INFORMIXSERVER=online1

export INFORMIXSERVER

## Echo message to log file

echo "Archive Informix IDS for $INFORMIXSERVER"

## Check for valid backup device, prevents accidentally overwriting

set `grep "^TAPEDEV" $INFORMIXDIR/etc/$ONCONFIG`

if [ "$2" != "/u3/backup/online1.bak" ]

then

echo "Invalid TAPEDEV $2"

date

else

echo "Archive to TAPEDEV $2"

date

## Start ontape and respond to prompts - the following spacing is key

## There must be a 0 for the level followed by blank line for the

## the response to the prompt.

{

ontape -s <<EOF

0

EOF

} | tail -5

## Only read the last 5 lines to prevent filling up your log when errors

echo "Archive Completed"

fi

Restoring an IDS server

IDS must be off-line to perform a restore. The restore will wipe out all your current data and configuration. The disk layout must be exactly configured the same as when you created the backup.

Note: Before you begin, write protect your tape. The restore process has a confusing prompt asking "do you want to backup your logical logs?". I know DBA's who have responded yes to this prompt and accidentally wiped out their restore tape.

The command to start a restore is:

ontape -r

Figure 39 shows the full dialog of prompts during the restore process.

Figure 39: Performing an IDS restore

informix@merlin >ontape -r

Please mount tape 1 on /dev/rmt/0 and press Return to continue ...

Archive Tape Information

Tape type: Archive Backup Tape

Online version: INFORMIX-OnLine Version 7.13.UC2

Archive date: Fri Sep 27 17:48:39 1996

User id: informix

Terminal id: /dev/pts/0

Archive level: 0

Tape device: /dev/rmt/0

Tape blocksize (in k): 16

Tape size (in k): 2000000

Tape number in series: 1

Spaces to restore:1 [rootdbs ]

Archive Information

INFORMIX-OnLine Copyright© 1986-1996 Informix Software, Inc.

Initialization Time 09/03/96 17:31:15

System Page Size 2048

Version 4

Archive CheckPoint Time 09/27/96 17:48:44

Dbspaces

number flags fchunk nchunks flags owner name

1 1 1 1 N informix rootdbs

Chunks

chk/dbs offset size free bpages flags pathname

1 1 0 50000 38641 PO- /u3/dev/dbspace713

Continue restore? (y/n)y

Do you want to back up the logs? (y/n)n

Restore a level 1 or 2 archive (y/n) n

Do you want to restore log tapes? (y/n)y

Roll forward should start with log number 9

Please mount tape 1 on /dev/rmt/0 and press Return to continue ...

Do you want to restore another log tape? (y/n)n

Program over.

informix@merlin >onstat -

INFORMIX-OnLine Version 7.13.UC2 -- Quiescent -- Up 00:10:35 -- 8976 Kbytes

First ONTAPE will display the disk configuration as it was when the backup was performed. This gives you a chance to verify that you have created the correct devices and links.

Next ONTAPE will prompt you if you would like to backup logical logs. This prompt is very confusing if you have not done this a few times. If your system had crashed and IDS was able to backup additional logs it will help you in the recovery process. Put a NEW tape in the drive and respond yes. Do NOT leave your restore tape in or IDS may overwrite it. If you do not want to backup any current logs, respond NO.

Now ONTAPE will start the restore. This is no progress report on the restore like there is on the backup. Be patient and wait. If more than one tape is required you will be prompted for it.

After the level 0 tape has been restored ONTAPE will ask if you have a level 1 or 2 backup to restore.

When all backup levels have been restored, ONTAPE will prompt you for logical log tapes to restore. Start with the tape containing the logical log you are prompted for. You cannot skip logical logs or restore them in a different order. If you do not have any logical log backups simply respond NO to these prompts.

Finally, IDS will start to roll forward, or roll back any transactions necessary. When this is completed the IDS server will be in quesicent mode ready for you to check.

Backing up logical logs

ONTAPE is also used to backup logical logs. The logical log backup device is controlled by the following parameters in your ONCONFIG file:

LTAPEDEV /dev/tapedev # Logical Log tape device path (e.g /dev/rmt/0)

LTAPEBLK 16 # Log tape block size (Kbytes)

LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)

When you do not want to backup Logical Logs to tape, set LTAPEDEV to equal "/dev/null". IDS understands this and frees the logical logs as soon as they can be reused without a backup. Otherwise, you must backup your logical logs before they can be reused.

Note: When all Logical Logs are full IDS will halt all processing until you backup the logs. This will stop all user activity.

There are two forms of Logical Log Backup:

1) Continuous Backup (ontape -c) - this runs the ontape process backing up logical logs non-stop until you stop.

2) Automatic Backup (ontape -a) - the ONTAPE process runs backing up all logical logs that need to be backed up. Once all logs have been backed up the process stops.

Issues with continuous backup of logical logs to tape

Continuous backups requires a dedicated terminal or window in which it runs. This is where it will display tape change prompts and expect an operator to respond to these prompts. It also requires a dedicated tape drive and an operator who will monitor the progress of its backups. The operator must carefully label tapes so they can be used in a restore. To stop continuous backups simple enter "Control-C" or the interrupt character on your system.

Anytime the continuous backups is aborted and restarted a new tape must be used or else the old tapes will be overwritten. Each execution of continuous or automatic backups requires a new tape. ONTAPE backups cannot append to the end of the last tape. Continuous backups must also be restarted with a new tape after your system is rebooted.

Issues with automatic manual backup of logical logs to tape

Automatic backup of logs requires a dedicated terminal and tape drive only during the actual backups. This tape drive may be shared with other activities. However, it still requires an operator to monitor the logs, and start a backup before they all become full. It also requires careful labeling of tapes and coordinating with ontape archives. Figure 40 shows the screen display of the automatic backup.

Figure 40: Automatic backup of logical logs

Performing automatic backup of logical logs.

Please mount tape and press Return to continue ...

This tape contains the following logical logs:

11 - 12

Please label this tape as number 1 in the log tape sequence.

Please mount next tape and press Return to continue ...

*** The tape was not changed ***

Please mount next tape and press Return to continue ...

This tape contains the following logical logs:

12 - 14

Please label this tape as number 2 in the log tape sequence.

Please mount next tape and press Return to continue ...

This tape contains the following logical logs:

14 - 16

Please label this tape as number 3 in the log tape sequence.

Changing logging mode for a database

ONTAPE is also used to change the logging mode of a database. To change a database from no logging to some form of logging requires a backup. The command to perform a backup and change a database from no logging to buffered logging is:

ontape -s -B database_name

If you want to change the logging mode of a database without performing a complete backup simply change TAPEDEV in your ONCONFIG file to "/dev/null". Then run the ONTAPE command listed above. Be sure to change TAPEDEV back to its original after you are done.

ONUNLOAD and ONLOAD - Unloading and loading databases and tables

The ONUNLOAD utility and the corresponding ONLOAD utility provide a way to save whole tables or databases in a binary format to tape or disk. These two utilities copy whole pages from dbspaces and save the results in binary format. They must work together. Only ONLOAD can read and load the results of an ONUNLOAD. If you need to unload data in ASCII or text format use the SQL unload statement of DBEXPORT.

The advantage of these two utilities is that they are fast. They copy whole pages of data including existing indexes structures. This makes it very fast to reload because indexes do not need to be rebuilt. Also, since the output is stored in binary format, there is some security in the data being protected.

There are a few drawbacks to these two utilities:

Data is not portable. It can only be loaded using the same version of IDS on the same type of machine. Since the data is stored in binary format the operations need to be performed on computers that are binary compatible.

Data is not compressed. Since the data is copied in whole pages, empty data space and empty index structures on a page remain and are reloaded on the target system. SQL unload and load will rebuild the data on pages and rebuild all indexes compressing the data.

The utilities will require an exclusive lock on the table or database being unloaded and loaded.

These utilities are useful in several ways:

They provide fast table level backups.

They provide fast database level backups.

When transferring tables or databases to other systems with different dbspace layouts.

When moving databases or tables from one dbspace to another.

Figure 41 contains the syntax for the onunload command and Figure 42 contains the syntax for the onload command.

Figure 41: ONUNLOAD syntax

onunload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>]

<database> [:[<owner>.]<table>]

-l Use logical log tape configuration from ONCONFIG file

-t Tape devices overriding TAPDEV in ONCONFIG

-b Tape block size overriding size in ONCONFIG

-s Tape size overriding size in ONCONFIG

Figure 42: ONLOAD syntax

onload [-l] [-t <tape device>] [-b <block size>] [-s <tape size>]

[-d <Dbspace>]

<database>[:[<owner>.]<table>] [{-i <old indexname> <new indexname>}]

[{-fd oldDBspname newDBspname}]

[{-fi indexname oldDBspname newDBspname}]

-l Use logical log tape configuration

-t Tape devices

-b Tape block size

-s Tape size

-d DBspace name

-i Rename index during load

-fd Change data fragment dbspace

-fi Change index fragment dbspace

As an example of using these utilities let's take the steps required to move the items table in the stores7 database from one dbspace to another. These utilities are perfect for this task because they are fast.

First we need to unload the items table. ONUNLOAD requires that the file exist if you are unloading to disk. Our first step is to create an empty file using the UNIX touch command. If you are unloading to tape you can skip this step because the tape device already exists.

touch items.onunload

Next we execute the unload:

onunload -t items.onunload stores7:items

This will create a binary image of the items table using our file.

Now we need to use SQL and dbaccess to drop the items table. We are piping the SQL statements to dbaccess.

dbaccess stores7 - <<EOF

drop table items;

EOF

The final step is to reload the items table into a new dbspace. For this example we will load it into a dbspace named itemsdbs.

onload -t items.onunload -d itemsdbs stores7:items

This will create the items table in the new dbspace.

Read More......