Cover V04, I01
Article
Figure 1
Figure 2
Figure 3
Listing 1
Listing 2

jan95.tar


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 ---------------------------------------#