Using SQL to Generate SQL Scripts (DBA Tricks and Tips)

Published on July 1, 1997 by Lester Knutsen

First published in the Washington Area Informix User Group Newsletter
Volume 7, No.3 - July 1997

This article will take a look at some tricks for using SQL to generate SQL to help you maintain and support your databases. One of the advantages of querying the system tables is that you can use the output of a query to generate SQL. This is very useful when you have to change database privileges, or change the locking mode of all tables in a database.

Using SQL to create an SQL script to change page level locking

When a table is first created, a locking mode is defined. The two lock modes are 1) page level - all rows on a page are locked when one row is locked, or 2) row level - only the one row in use is locked. The default is page level locking for performance reasons. However to maximize concurrence, a DBA will often need to change this to row level locking. The first example will be to look at a script to 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 to row level locking.

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

{
#################################################################
# Module: %W% Date: %D%
# 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
        tabname , -- tablename
        " 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

 

This is what lockmod.sql looks like after the script has been run on the stores7 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);

 

Using SQL to generate scripts to revoke privileges

Another Informix default that I often need to change is "public" privileges. When a table is created, by default public is granted select, update, delete, and insert on that table. This means that any user can who can access the database can modify all data in that table. Example 2 is an SQL script which generates the SQL to revoke all public table privileges on a database. Since it reads the system tables to create the script, this script can be run on any database. One note: because you cannot revoke privileges granted by someone else, the script limits itself to tables where 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.

Example 2. SQL script to generate another SQL script to revoke all public privileges.

 

{
#################################################################
# Module: %W% Date: %D%
# 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
        tabname, -- Table name from systables
       " from public;"
from systables , systabauth
where systables.tabid = systabauth.tabid
and grantee = "public" -- Select table names were 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

 

This is what revokepub.sql looks like 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;

 

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 (INFORMIX-SE), grant privileges, and automate many other DBA tasks.

Getting Column Names and 4GL Data Types with the vi Editor

How often have you tired of retyping the same database column names repeatedly in vi? One solution is to save the column names in a file and read them in as needed. However, vi has the capability to insert into a document the results of an operating system command. With a little work with shell scripts and awk, you can create a command that will get the column names from any table in any database, and insert them into your current document. And with a little more work, you can add formatting like commas, data types, or the INFORMIX-4GL format of "variable name like tablename.columnname".

To insert the results of a UNIX command on the current line in vi, use "!!command". For example, start vi and type "!!ls". This inserts the names of the files in the current directory into your document. To run the INFORMIX-SQL info command for the item table in the stores7 database, and insert the results into the current document, you would do the following:

1. Create an SQL script called itemsinfo.sql with one line as follows:

info columns for items;

 

2. Load vi and type the following:

!!dbaccess stores7 itemsinfo

 

3. This runs the sql script and inserts the results into the current document.

The following shell script is one I have created to get the column names from a database for SQL, ESQL/C, and 4GL programs. As long as the script getcol is in my path, I can get column names from any table. Type in the script, put it in your path, and from within vi type "!!getcol databasename tablename". I use awk to format the output so it is more useful. The script can also be run from the UNIX command line and the output will display on screen.

The following are two examples of running the script from a UNIX prompt. The first one returns just the column names, the second example with the "-d" option returns the data types.

lester@merlin >getcol stores7 items
item_num,
order_num,
stock_num,
manu_code,
quantity,
total_price

lester@merlin >getcol stores7 items -d
item_num smallint,
order_num integer,
stock_num smallint,
manu_code char(3),
quantity smallint,
total_price money(8,2)

 

#!/bin/sh
#############################################################################
# PROGRAM: getcol - DataTools_Get_Columns, this will get the columns from
# an Informix database in a format ready to use in a sql
# query, a 4gl program etc...
# In vi, put the cursor on a blank line where you want
# to insert the columns and type !!getcol database columns.
# The columns will be inserted into the current file.
# USAGE: getcol database table [-d\-l]
# -d displays as column data type
# -l displays as column like table.column
# The default displays column names only
# AUTHOR: Lester B. Knutsen, Advanced DataTools Corporation
# email lester@advancedatatools.com
# Copyright (c) 1993, Advanced DataTools Corporation, All rights reserved.
#############################################################################
# function to display usage error and exit
usage() {
echo "usage: $1 database table [-d|-l]"
echo "\t-d: column data type\n\t-l: column like table.column" ; exit 1
}
#############################################################################
# test for correct number of arguments
[ $# -lt 2 ] || [ $# -gt 3 ] && { usage $0
}
# test for valid third argument ( -l or -d )
case $3 in
-d|-l|"") ;;
*) usage $0 ;;
esac
#############################################################################
# use the info columns command to get the data from informix and pipe to awk
# check for dbaccess or isql
SQL=isql;
[ -f $INFORMIXDIR/bin/dbaccess ] && SQL=dbaccess
{ echo $3 $2; $SQL $1 - 2>/dev/null <1 && $1 > "" && $1 != "Column" && $1 != "Error" && $1 != "Near" {
# add the comma and a newline to the end of the last line
if ( outcnt >= 1 ) printf(",\n")
# option -d print column and data type
if ( opt == "-d" ) printf("%-18s\t%s",$1 , $2)
# option -l print column like table.column
else if ( opt == "-l" ) printf("%-18s\tlike %s.%s",$1 , tab, $1 )
# no option print column name only
else printf("%s",$1)
outcnt++
}
# print a newline at the end
END { print "\n" } '
#############################################################################