If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Use these tricks to generate IDS SQL scripts
By Lester Knutsen

All good DBAs have some tricks up their sleeves to make their jobs easier. One I like to use is to write an SQL query of the Informix system tables and use the output of that query to generate another SQL script. This trick saves a lot of time (and reduces the possibility of missing a table) when you’re working with a large database with many tables.

Let’s say you need to change all the tables in a database from page-level to row-level locking, or that you need to defer foreign key constraints on all the tables to do a load. You could write an SQL script to do this one table at a time, but with a few hundred tables this task quickly becomes lengthy and tedious. Or, you could write an SQL statement to query the system tables and generate the required SQL script for you. I’ll show you three examples for which this approach is particularly useful. First, when changing the locking mode of all tables in a database and, second, when changing database privileges. The third example shows how to generate a script to enable or disable all the constraints of all the tables in a database. This script can be very helpful when you need to load or change data en masse and constraints are preventing that from being accomplished.

Changing Page-Level Locking

When a table is first created, a locking mode is defined. The two lock modes are page level (all rows on a page are locked when one row is locked) and row level (only one row in use is locked). Page-level locking is the default for performance reasons. However, to maximize concurrence, a DBA will often need to change to row-level locking. You can create a script to first check the lock level of all tables in a database and then, for any tables that use page level locking, to generate the SQL code to alter that locking mode to row level. Listing 1 shows the SQL script that generates the second SQL script, which will change the lock mode of all tables to row-level locking. Listing 2 shows the output that results.

The basic process in all these scripts is to use the system tables combined with text strings in SQL to output a text file with SQL commands. You can create scripts to unload data, alter extent sizes, check tables’ grant privileges, and automate many other DBA tasks.

In Listing 1, the lines output to lockmod.sql without headings create a file with the SQL commands to execute what you need done, without the normal column headings that SQL produces. Note that you’ll use dbaccess to create the output, and dbaccess wraps the output file at 80 characters. Many SQL command lines will be longer then 80 characters, but this wrapping doesn’t cause a problem with the SQL generated — it just affects the way it looks.

Generating Scripts to Revoke Privileges

Another Informix default that I often need to change is public. When a table is created, public is granted select, update, delete, and insert privileges on that table by default. In other words, any user who can access the database can modify all data in that table. Listing 3 shows an SQL script that generates the SQL to revoke all public table privileges on a database.

Because it reads the system tables, this script can be run on any database. However, you can’t revoke privileges granted by someone else, so the script limits itself to tables on which you granted the public privilege. If several developers are creating tables in a database, each developer will need to run this script to revoke public privileges. The script can be modified to grant privileges to specific users or a set of users. Listing 4 shows the output that results from running this script.

Generate Scripts to Enable or Disable Constraints

On a large database with hundreds of tables, it would take a lot of typing to write out all the SQL commands to enable or disable constraints. Listing 5 is a little SQL script that may save you a bit of time and effort. This script will generate the SQL statements to enable or disable all the foreign key constraints in a database.

Script Away

I’ve shared just a few examples of the many scripts you can create using SQL with the system tables. On a large database with many tables, creating scripts by hand is tedious and error prone. Creating scripts using the system tables that store information about all the tables and columns in a database ensures that all tables are included. Check the software section of International Informix User Group Web site for more examples.

Listings

Listing 1. SQL script to generate another SQL script to set row level locking for all database tables.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Generate SQL to set row level locking for all database tables
####################################################
}
output to lockmod.sql — Create SQL script
without headings — Don’t include column headings
select “alter table “, — Text
trim(tabname) , — Table name
” lock mode (row);” — Text
from systables
where tabid > 99 — Don’t get the systables
and tabtype = “T” — Get real tables not views
and locklevel = “P” — Get tables with page level locking
order by tabname

Listing 2. lockmod.sql after the script has been run on the stores_demo database.

alter table call_type lock mode (row);
alter table catalog lock mode (row);
alter table cust_calls lock mode (row);
alter table customer lock mode (row);
alter table items lock mode (row);
alter table log_record lock mode (row);
alter table manufact lock mode (row);
alter table orders lock mode (row);
alter table state lock mode (row);
alter table stock lock mode (row);

Listing 3. SQL script to generate another SQL script to revoke all public privileges.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Revoke all public privileges for all tables
####################################################
}
output to “revokepub.sql”
without headings
select “revoke all on “, — Revoke all privileges
trim(tabname) , — Table name from systables
“from public;”
from systables , systabauth
where systables.tabid = systabauth.tabid
and grantee = “public” — Select table names where public has privileges
and grantor = USER — Only revoke the ones you have privilege to revoke
and systables.tabid > 99 — Don’t revoke the system table privileges

Listing 4. revokepub.sql after the script has been run on the stores7 database.

revoke all on customer from public;
revoke all on orders from public;
revoke all on manufact from public;
revoke all on stock from public;
revoke all on items from public;
revoke all on state from public;
revoke all on call_type from public;
revoke all on cust_calls from public;
revoke all on custview from public;
revoke all on log_record from public;
revoke all on catalog from public;

Listing 5. SQL script to generate SQL script to disable foreign key constraints.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Disable and enable all tables foreign key constraints
####################################################
}
output to ” disable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “disabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;
output to ” enable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “enabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;

Lester Knutsen
is president of Advanced DataTools Corp., an IBM Informix consulting
and training partner specializing in data warehouse development,
database design, performance tuning, and Informix training and support.
He is president of the Washington D.C. Area Informix User Group, a
founding member of the International Informix Users Group, and an IBM
gold consultant.