Listing 1: create.diskinit
#!/bin/sh
#-----------------------------------------------------------------------------#
#- Author of this Program: William Genosa -#
#- Program Name and Release: @(#)create.diskinit 1.3 -#
#- File Name Used by SCCS: s.create.diskinit -#
#- Tested on Sybase Versions: 4.2 and 4.92 for AIX -#
#- Last Date of Modification: 94/05/31 -#
#- -#
#- Description: -#
#- This program will extract disk information from the master database and -#
#- create the disk init sql that would re-create the physical to logical -#
#- configuration of the server. The disk init command initializes physical -#
#- disk space into virtual database space. Virtual database space is defined -#
#- in 2k pages that are grouped into allocation units of 256 pages per unit. -#
#- Physical to virtual mapping is stored in two sybase system tables called -#
#- sysdevices and sysusages. Information from these two tables will be used -#
#- to re-create the disk init sql script. -#
#- -#
#- This program is broken into five sections. -#
#- -#
#- Section 1 - Set up environment variables. -#
#- Section 2 - Creates a temporary script which will generate sql. -#
#- Section 3 - Retrieves device information from the database. -#
#- Section 4 - Executes the script which generates the disk init sql. -#
#- 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 -#
#- the section of code which creates the header. -#
#- -#
#- /******************************************************************/ -#
#- /* Program Name and Release: @(#)diskinit.sql 1.1 */ -#
#- /* File Name Used by SCCS: s.diskinit.sql */ -#
#- /* Last Date of Modification: 94/05/31 */ -#
#- /******************************************************************* -#
#- log01:N/A -#
#- PV COPIES IN BAND DISTRIBUTION -#
#- hdisk1 275:000:000 23% 036:064:065:045:065 -#
#- *******************************************************************/ -#
#- disk init name = "log01", -#
#- physname = "/dev/rlog01", -#
#- vdevno = 2, -#
#- size = 563200 -#
#- 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. As mentioned, -#
#- this program creates the disk init sql. Other programs which could create -#
#- segments, tables, indexes, 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=/accts/scr/scripts/.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 #- Sybase directory structure location. -#
DSQUERY=ACCTS #- Locates the server in interface file. -#
LOGIN=sa #- Sybase sa login. -#
PASSWORD=guessme #- The password for sa. -#
QUERYOUT=/tmp/query #- Holds output of a query. -#
PROGRAM=/tmp/program #- Temporary executable that creates sql. -#
SQLDIR=/acct/src/sql #- Directory where output should go. -#
export SYBASE DSQUERY LOGIN PASSWORD QUERYOUT PROGRAM
fi
SQL=${SQLDIR}/diskinit.sql #- The generated sql script. -#
export SQL
#------------------------------ Section 2 ------------------------------------#
#- This section of code will create a temporary executable file which will -#
#- be saved in the file assigned to the variable PROGRAM. The PROGRAM will -#
#- generate sql code containing the disk init command. When PROGRAM is -#
#- executed, it will take input from the file assigned to the variable -#
#- QUERYOUT. The input file assigned to QUERYOUT is loaded with information -#
#- retrieved from the database. That information is in the following format: -#
#- -#
#- sybase_name physical_name device_number size_in_2k_pages -#
#- ----------- ------------- ------------- ---------------- -#
#- log01 /dev/rlog01 2 563200 -#
#- -#
#- The syntax of a disk init command is as follows: -#
#- -#
#- disk init name = "sybase_name", -#
#- physname = "physical_name", -#
#- vdevno = device_number, -#
#- size = size_in_2k_pages -#
#- -#
#- Because this is an IBM RS-6000, the logical volume information is also -#
#- included with the name of the physical volume as well as the logical -#
#- volume distribution. If you wish to run this program on a Sun platform, -#
#- remove the lines which have been tagged as RS-6000 specific. The final -#
#- command in this section gives execute permission to the created script. -#
#-----------------------------------------------------------------------------#
echo "while read line
do
name=\`echo \${line} | awk '{print \$1}'\`
physname=\`echo \${line} | awk '{print \$2}'\`
vdevno=\`echo \${line} | awk '{print \$3}'\`
size=\`echo \${line} | awk '{print \$4}'\`
#-------------------- Begin RS-6000 Specific Code ---------------------------#
logvol=\`echo \${line} | awk '{print \$2}' | awk '-F/' '{print \$3}' | \
sed \"s/^r//g\"\`
if lslv -l \${logvol} 1>/dev/null 2>/dev/null
then
echo \"/*******************************************************************\"
lslv -l \${logvol} | sed \"s/^/ /g\" 2>/dev/null
echo \"*******************************************************************/\"
else
echo \"/******************************************************************/\"
fi
#---------------------- End RS-6000 Specific Code ---------------------------#
echo \"disk init name = \\\"\${name}\\\",\"
echo \" physname = \\\"\${physname}\\\",\"
echo \" vdevno = \${vdevno},\"
echo \" size = \${size}\"
echo \"go\"
echo \" \"
done < ${QUERYOUT}" > ${PROGRAM}
chmod 700 ${PROGRAM}
#------------------------------ Section 3 ------------------------------------#
#- This section executes the query below, filters the output and populates -#
#- the file assigned to the QUERYOUT variable. The query joins 2 tables in -#
#- the master database (system catalog). Those tables are sysdevices and -#
#- sysusages. The sysdevices table has one entry for each database -#
#- device. The sysusages table accounts for the servers disk allocation. -#
#- The attributes are projected to columns named QQZZ so the header returned -#
#- by the server can be easily filtered out. The name and phyname attributes -#
#- are truncated with substring so each device will be returned with only -#
#- one line of output. The column QQZZ3 is the device number. Device numbers -#
#- are assigned by the sa. Device numbers must be unique numbers between 1 -#
#- and 255. The device number determines the sybase starting virtual page -#
#- address. The following calculation is an example of how to determine the -#
#- starting sybase virtual page number (sysdevices.low) for a device number -#
#- which is 13. -#
#- device number = 13 in base 10 -#
#- 13 = D in hex -#
#- 0xD * 0x1000000 = 218103808 base 10 -#
#- 218103808 = sysdevices.low (starting logical page) -#
#- -#
#- The column QQZZ4 is the size of the database device in 2k pages. The -#
#- calculation to determine the size is shown below. -#
#- -#
#- size in 2k pages = sysdevice.high - sysdevices.low + 1 -#
#- -#
#- The join is restricted by using the where clause to specify which of the -#
#- rows to return. In this case, rows returned will have a virtual starting -#
#- address in sysusages between the low and high address of sysdevices, as -#
#- well as being a special device which is either a default or non-default -#
#- disk. The union operator is used to combine results for both non- -#
#- default and default devices repectively. The query is passed directly to -#
#- the client isql and the result from the server is filtered with egrep to -#
#- remove the header and trailing blank line. The filtered output is saved -#
#- to the file assigned to QUERYOUT. -#
#-----------------------------------------------------------------------------#
echo "set nocount on
use master
go
select QQZZ1 = substring(d.name, 1, 24),
QQZZ2 = substring(d.phyname, 1, 32),
QQZZ3 = convert(tinyint,substring(convert(binary(4),d.low),1,1)),
QQZZ4 = d.high - d.low + 1
from sysdevices d, sysusages u
where u.vstart between d.low and d.high
and d.cntrltype = 0 and d.status = 2
union
select QQZZ1 = substring(d.name, 1, 24),
QQZZ2 = substring(d.phyname, 1, 32),
QQZZ3 = convert(tinyint,substring(convert(binary(4),d.low),1,1)),
QQZZ4 = d.high - d.low + 1
from sysdevices d, sysusages u
where u.vstart between d.low and d.high
and d.cntrltype = 0 and d.status = 3
order by QQZZ3
go" | ${SYBASE}/bin/isql -U${LOGIN} -P${PASSWORD} | egrep -v "QQZZ|\-|^$" | \
awk '$3 > 0 {print}' > ${QUERYOUT}
#------------------------------ Section 4 ------------------------------------#
#- The program created in Section 2 is now executed to generate the diskinit -#
#- sql statements based on the information retrieved from the database in -#
#- Section 3. The output file assigned to the varaible SQL is truncated and -#
#- a header is placed in the file containing sccs keyword identifiers. The -#
#- PROGRAM is executed and appended to the file assigned to SQL. Sed is used -#
#- to replace the question marks with percent signs to prevent sccs from -#
#- expanding the keywords when this program, create.diskinit, is checked -#
#- into sccs. The keywords placed in the header of the created SQL file -#
#- are to be used when checking the SQL file into sccs. -#
#-----------------------------------------------------------------------------#
> ${SQL}
echo "/******************************************************************/" \
>> ${SQL}
echo "/* Program Name and Release: ?W? */" | \
sed "s/?/%/g" >> ${SQL}
echo "/* File Name Used by SCCS: ?F? ?Y? */" | \
sed "s/?/%/g" >> ${SQL}
echo "/* Last Date of Modification: ?D? ?Q? */" | \
sed "s/?/%/g" >> ${SQL}
${PROGRAM} >> ${SQL}
echo "/******************************* END ******************************/" \
>> ${SQL}
#------------------------------ Section 5 ------------------------------------#
#- The files assigned to PROGRAM and QUERYOUT are no longer needed. System -#
#- Administrators should appreciate the removal of files no longer needed. -#
#-----------------------------------------------------------------------------#
rm -f ${QUERYOUT}
rm -f ${PROGRAM}
#-----------------------------------END---------------------------------------#
|