Listing 1: create.index--Creates indexes for single table
#!/bin/sh
#-----------------------------------------------------------------------------#
#- Author of this Program: William Genosa -#
#- Program Name and Release: @(#)create.index 2.5 -#
#- File Name Used by SCCS: s.create.index -#
#- Tested on Sybase Versions: 4.2 and 4.92 for AIX -#
#- Last Date of Modification: 94/07/29 -#
#- -#
#- Description: -#
#- This program will retrieve table information from the master database and -#
#- generate the sql required to re-create all the indecies for the table -#
#- passed to this script as the first augment. -#
#- -#
#- This program is broken into five sections. -#
#- -#
#- Section 1 - Set up environment variables. -#
#- Section 2 - Make sure we have a table name as the first augment. -#
#- Section 3 - Retrieve the names and type of index for the table. -#
#- Section 4 - Retrieve the attribute, option, and segment info -#
#- from the database and generate the sql required to -#
#- re-create each index. -#
#- Section 5 - Clean up temporary files used. -#
#- -#
#- This program is run in the bourne shell to remain compatable across unix -#
#- platforms. Final output of this program will appear as shown below, once -#
#- it has been checked into sccs. If you do not wish to use sccs, then omit -#
#- section of code which creates the header. -#
#- -#
#- /************************************************************* -#
#- Program Name and Release: @(#)sysobjects.idx 1.1 -#
#- File Name Used by SCCS: s.sysobjects.idx -#
#- Last Date of Modification: 94/07/29 -#
#- *************************************************************/ -#
#- CREATE UNIQUE CLUSTERED INDEX sysobjects -#
#- ON sysobjects ( -#
#- id -#
#- ) -#
#- ON system -#
#- go -#
#- /***************************END******************************/ -#
#- -#
#- /************************************************************* -#
#- Program Name and Release: @(#)ncsysobjects.idx 1.1 -#
#- File Name Used by SCCS: s.ncsysobjects.idx -#
#- Last Date of Modification: 94/07/29 -#
#- *************************************************************/ -#
#- CREATE UNIQUE NONCLUSTERED INDEX ncsysobjects -#
#- ON sysobjects ( -#
#- name, -#
#- uid -#
#- ) -#
#- ON system -#
#- go -#
#- /***************************END******************************/ -#
#------------------------------ Section 1 ------------------------------------#
#- In this section we assign our environment variables. Because this program -#
#- may be one of several programs which is to be executed using the same -#
#- variable assignments, provisions have been made for variable assignments -#
#- to be done within this program or via an external file. The script -#
#- generates sql that would re-create all the indecies for the table passed -#
#- as the first augment. Other programs which could create tables, segments, -#
#- rules, triggers, stored procedures, defaults, and databases may read from -#
#- the same CONFIG file for variable assignment. Then only one CONFIG file -#
#- would need modification instead of editing each program individually. -#
#- Only variables which would be considered global are to be kept in the -#
#- file assigned to the variable CONFIG. Notice the use of the dot command -#
#- for exporting variables in the CONFIG file. -#
#-----------------------------------------------------------------------------#
CONFIG=/home/bill/.config #- Global variable configuration file. -#
if [ -f "${CONFIG}" ] #- If the global variable configuration -#
then #- exists, then use it. Otherwise set up -#
. ${CONFIG} #- the variables locally in this program. -#
else
SYBASE=/home/sybase #- The sybase directory structure location. -#
DSQUERY=CLIENT #- Locates the server in interface file. -#
LOGIN=sa #- Sybase sa login. -#
PASSWORD=guessme #- The password for sa. -#
SERVER=CLIENT #- The Sybase SQL Server to access. -#
DATABASE=accounts #- The database to access. -#
export SYBASE DSQUERY LOGIN PASSWORD SERVER DATABASE
fi
TABLE=$1
WORK=/tmp/work
COUNT=1
export TABLE WORK COUNT
#------------------------------ Section 2 ------------------------------------#
#- Make sure a table name has been passed as the first augment. -#
#-----------------------------------------------------------------------------#
if [ "$#" -ne "1" ]
then
echo "usage $0: <tablename>"
exit
fi
#------------------------------ Section 3 ------------------------------------#
#- The first augment of the program must be a table name. This part of the -#
#- program will retrieve the names and types of indecies for the specified -#
#- table and save them in a file. The while loop will strip off the header -#
#- and awk will insure no table names are output to the file. Therefore the -#
#- format of the output file will be as follows: -#
#- -#
#- indexname 1 -#
#- indexname 2 -#
#- indexname 3 -#
#- -#
#- The indexname is as implies, the name of the index. The number which -#
#- follows the index name is the index id in sysindexes. Index id's have -#
#- the following meaning: -#
#- -#
#- 0 A table with no clustered index. -#
#- 1 A clustered index. -#
#- 2 and above A nonclustered index. -#
#- -#
#- Notice that the output file is truncated before appending data into the -#
#- file. This is good defensive programming. -#
#-----------------------------------------------------------------------------#
>${WORK}
echo "set nocount on
select i.name, i.indid
from sysobjects o, sysindexes i
where o.id = i.id
and o.name = '$1'
go" | ${SYBASE}/bin/isql -U${LOGIN} -P${PASSWORD} | \
while read line
do
if [ "${COUNT}" -ge "3" ]
then
echo ${line}
COUNT=`echo "${COUNT} + 1" | bc`
else
COUNT=`echo "${COUNT} + 1" | bc`
fi
done | awk 'NF > 1 && $2 != 0 {print $0}' >> ${WORK}
#------------------------------ Section 4 ------------------------------------#
#- The file assigned to the variable WORK will contain the name and type of -#
#- each index. Using that information, retrieve from the database all the -#
#- data required to generate the sql required to recreate that index and -#
#- save it in a file with the index name and an idx extension. The query is -#
#- passed off to awk which assigns all the attributes used in the creation -#
#- of that index to the awk variable STRING. Awk also insures that the -#
#- proper sql syntax is used, the proper options are included, and that -#
#- SCCS keyword identifiers are added in the header for source code control. -#
#-----------------------------------------------------------------------------#
while read line
do
INDEX=`echo ${line} | awk '{print $1}'`
INDID=`echo ${line} | awk '{print $2}'`
> ${INDEX}.idx
echo "/***********************************************************************/
/* Setting nocount on will eliminate the rows affected message. */
/*****************************************************************************/
set nocount on
/*****************************************************************************/
/* In sql, local variables must be declared with the proper data type before */
/* they can be used. */
/*****************************************************************************/
declare @mesg1 char(20)
declare @mesg2 char(20)
declare @mesg3 char(20)
declare @mesg4 char(20)
declare @mesg5 char(20)
declare @mesg6 char(20)
declare @count int
declare @attribute varchar(30)
declare @index varchar(200)
declare @return char(20)
declare @segment varchar(30)
declare @location varchar(30)
/*****************************************************************************/
/* Once the variable is declared, it can then be initialized to a value. */
/* Because this sql script is echoed into the isql client interface, the */
/* double quotes must be escaped so the shell does not interpet them. */
/*****************************************************************************/
select @mesg1 = \"ignore_dup_key\" /* Option, cancels insert of dup keys */
select @mesg2 = \"UNIQUE\" /* Keyword, insures no duplicate keys */
select @mesg3 = \"ignore_dup_row\" /* Option, cancels insert of dup rows */
select @mesg4 = \"NONCLUSTERED\" /* Keyword, phy order != logical order */
select @mesg5 = \"allow_dup_row\" /* Option, allow insert of dup rows */
select @mesg6 = \"CLUSTERED\" /* Keyword, phys order = logical order */
select @index = \"\t\t\" /* Used for the storage of index keys */
select @count = 1 /* Used to count the keys, max of 16 */
select @return = \",\n\" /* Add a comma and newline to each key */
select @location = s.name /* The segment which the index resides */
from syssegments s, sysindexes i
where s.segment = i.segment
and i.indid = ${INDID}
and id in (select id
from sysobjects
where name = '${TABLE}')
select @segment = \"ON \" + @location
/*
** Ignore Duplicate Key
*/
select i.name, type = @mesg1
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and v.number = 1
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
/*
** Unique Index
*/
union
select i.name, type = @mesg2
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and v.number = 2
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
/*
** Ignore Duplicate Row
*/
union
select i.name, type = @mesg3
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and v.number = 4
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
/*
** Non-Clustered Index
*/
union
select i.name, type = @mesg4
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and v.number = 0
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
and i.indid > 1
/*
** Allow Duplicate Row
*/
union
select i.name, type = @mesg5
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and v.number = 64
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
/*
** Clustered Index
*/
union
select i.name, type = @mesg6
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = \"I\"
and i.id in (select id
from sysindexes
where id in (select id
from sysobjects
where name = '${TABLE}'))
and i.indid = ${INDID}
and i.indid = 1
while @count <= 16
begin
select @attribute = index_col('${TABLE}', ${INDID}, @count)<R>
if @attribute = NULL
goto finished
if @count > 1
select @index = @index + @return
select @index = @index + index_col('${TABLE}', ${INDID}, @count)
select @count = @count + 1
end
finished:
print @index
print @segment
go" | ${SYBASE}/bin/isql -U${LOGIN} -P${PASSWORD} | \
awk 'BEGIN { STRING = ""; \
RETURN = "\n"; \
SPACE = " " } {
if ( NF == "1" ) {
DOMAIN=$1
STRING = STRING RETURN SPACE DOMAIN
}
if ( NF == "2" && $2 == "CLUSTERED" ) {
CLUSTER="CLUSTERED"
}
if ( NF == "2" && $2 == "NONCLUSTERED" ) {
CLUSTER="NONCLUSTERED"
}
if ( NF == "2" && $2 == "UNIQUE" ) {
UNIQUE="UNIQUE "
}
if ( NF == "2" && $2 == "ignore_dup_key" ) {
IDUPKEY="WITH ignore_dup_key"
}
if ( NF == "2" && $2 == "ignore_dup_row" ) {
IDUPROW="WITH ignore_dup_row"
}
if ( NF == "2" && $2 == "allow_dup_row" ) {
ADUPROW="WITH allow_dup_row"
}
if ( NF == "2" && $1 ~ /ON/ && $2 != "default" ) {
SEGMENT=$1 " " $2
}
}
END { if ( STRING != "" ) {
print "/*************************************************************"
print "Program Name and Release: ?W?"
print "File Name Used by SCCS: ?F? ?Y?"
print "Last Date of Modification: ?D? ?Q?"
print "*************************************************************/"
printf ( "CREATE %s%s INDEX '"${INDEX}"'\nON '"$1"' (", \
UNIQUE, CLUSTER )
printf ( "%s\n", STRING )
printf ( ")\n" )
if ( IDUPKEY == "WITH ignore_dup_key" ) {
printf ( "%s\n", IDUPKEY )
}
if ( IDUPROW == "WITH ignore_dup_row" ) {
printf ( "%s\n", IDUPROW )
}
if ( ADUPROW == "WITH allow_dup_row" ) {
printf ( "%s\n", ADUPROW )
}
if ( SEGMENT ~ /ON/ ) {
printf( "%s\n", SEGMENT )
}
printf ( "go\n" )
print "/**************************END*******************************/"
}
}' | sed "s/?/%/g" >> ${INDEX}.idx
pg ${INDEX}.idx
done < ${WORK}
#------------------------------ Section 5 ------------------------------------#
#- The file assigned to the variable WORK is no longer needed. The System -#
#- Administrator should appreciate the removal of files no longer needed. -#
#-----------------------------------------------------------------------------#
rm -f ${WORK}
#--------------------------------- END ---------------------------------------#
|