SQL Tutorial 4.00150

What are scripts? How do we create them?

 

 

Introduction

SQL is a very powerful language that allows Database Administrators to manipulate data in a relational database.   SQL provides many powerful tools that make the job easier for a DBA; some of these tools are stored procedures and scripts.   We will focus on the latter.

 

Definition of script

In computer programming, a script is a program or sequence of instructions that is interpreted or carried out by another program rather than by the computer processor (as a compiled program is).   A script is sometimes used to mean a list of operating system commands that are prestored in a file and performed sequentially by the operating system's command interpreter whenever the list name is entered as a single command.  

 

In theory, a DBA could write enough scripts in order for the database to be managed by itself; in real life, scripts allow a DBA to do common maintenance and troubleshooting on a RDBMS.

Creation of a script

The creation of a script is straight forward, albeit difficult to do at first.   A script is created using a simple text editor, such as VI on UNIX or notepad on Windows.   Once the editor is running, you start by making comments on what the script does.   After that, its only a matter of typing the SQL commands needed to make the script do what the programmer wants it to do.   As with any language, SQL syntax must be followed carefully; debugging of a SQL script consists of running it and correcting mistakes as they are given by the RDBMS.   There are third-party software applications that can trace and debug a script before uploading it to the database server(s).

Example

The following script allows the DBA to see the free space for each tablespace, both in size and percent.

rem   name : freespace.sql 

rem   date : 01-01-1998

rem   desc : Shows free space for each tablespace (size and percent).

rem   compatible : v7.1 upwards

col name                 format A16 head "Tablespace Name"

col pct_used            format 999.9 head "Pct|Used"

col Kbytes               format 999,999,999 head "KBytes"

col used                  format 999,999,999 head "Used"

col free                    format 999,999,999 head "Free"

col max_free            format 999,999,999 head "Max size|free chunk"

break       on report

compute   sum of kbytes on report

compute   sum of free on report

compute   sum of used on report

select       nvl(FULL.tablespace_name,nvl(FREE.tablespace_name,'UNKOWN')) Name

               ,kbytes_used                    Kbytes

               ,kbytes_used-nvl(kbytes_free,0) Used

               ,nvl(kbytes_free,0)             Free

               ,((kbytes_used-nvl(kbytes_free,0)) / kbytes_used)*100 Pct_used

               ,nvl(max_free,0)                Max_free

from 

     ( select  sum(bytes)/1024    Kbytes_free

               ,max(bytes)/1024    max_free

               ,tablespace_name

       from   sys.DBA_FREE_SPACE

       group by tablespace_name ) FREE,

     ( select  sum(bytes)/1024    Kbytes_used

              ,tablespace_name

       from    sys.DBA_DATA_FILES

       group by tablespace_name ) FULL

where  FREE.tablespace_name (+) = FULL.tablespace_name

order by  name

/

Notice at the beginning how comments are created by using the rem command, very similar to DOS programming.   The section following the comments are the column definitions.   These are formatted and given header names (i.e. “Tablespace Name”).

The following section uses programs built into the RDBMS.   These particular examples compute the sum of Kbytes and many other functions are available; after that, its standard SQL.   There are several tutorials online but very, very few actually go into detail as to how to create an SQL script.   For the most part, people who are capable of writing scripts have an advance level of SQL and DBMS knowledge, and find writing scripts easy and straight-forward.

Further information

There is more to this of course when it comes to script creation and execution.   A lot of work must be made by the DBA in order for the scripts to execute correctly.   A proper operating environment must be set, that is, set the path where the scripts will reside.   After that is done, you have to setup the working directory to store the notes and programs and do test runs.

If all this is successful, then the DBA can program the DBMS to run the scripts at a predetermined day and time.

 

In UNIX for example, to set up the environment the DBA would do something like this:

 

depending on the shell, type the following command:

- for tcsh or csh: "source /opt/oracle/bin/coraenv"

- for ksh, bash or sh: ". /opt/oracle/bin/oraenv"

invoke the oracle tool needed, e.g for SQL*Plus: "sqlplus /"

 

The setup procedure would be something like this:

 

- create an Oracle working directory to store your notes and programs

- copy the script files, as an example, summdrop.sql, summit2a.sql

 

- from the directory ~liub/pub/teach/cs2102/ on the department's Unix machines. "summit2a.sql"

  has a number of tables for Oracle Tutorial I. "summdrop.sql" is for cleaning up, i.e., dropping   

  those tables, etc, created by "summit2a.sql".

 

- invoke SQL*Plus (command "sqlplus /") to verify you have an Oracle account type "quit;" to quit

  SQL*Plus

 

- run the summit2a.sql script against each database account: "sqlplus / @summit2a.sql"

- if you are inside oracle, run the summit2a.sql script with "@summit2a.sql"

 

For more detailed information, you can consult the many tutorials available online or the Oracle DBA books, available from Oracle Corp.

Q&A

 

Q: How many scripts can exists in a DBMS at any given time?

A: According to the DBA Guide from Oracle, scripts can occur as there is available hard drive

     space.   It is recommended by them that when scripts reach a high volume, that stored

     procedures be used instead.   This of course depends on the capacity of the servers hosting

     the database.

 

Q: How do scripts improve the management of a DBMS?

A: They help a DBA in the management of a DBMS in two ways: 1) it simplifies routine work that

     needs to be done to the DBMS and 2) allows the DBA to do other things that require more

     time or concentration to do.   This does not mean that the DBA leaves or can leave routine

     maintenance all to the scripts, since they are also capable of failing, as with any software.

 

Q: Is there anything else that I should know concerning scripts?

A: From the examples seen, scripts can get very complicated.  It is recommended that you test

     scripts on a test environment before you release them on a production database, since one

     false line or command can ruin the entire database.