How to execute sybase query or stored procedure on AIX Unix

Unix is a portable operating system that is designed for both efficient multi-tasking and mult-user functions.

Unix has the portability that allows it to run on different hardware platforms.

Kernel is the UNIX operating system. It is the master program that controls the computer’s resources, allotting them to different users and to different tasks. However, the kernel doesn’t deal directly with a user. Instead, it starts up a separate, interactive program, called a shell, for each user when he/she logs on.

A shell acts as an interface between the user and the system. As a command interpreter, the shell takes commands and sets them up for execution.

Visual representation of the UNIX operating system environment:

Some of the flavors of Unix are listed below:

  • AIX – developed by IBM for use on its mainframe computers
  • HP-UX – developed by Hewlett-Packard for its HP 9000 series of business servers
  • Linux – the most popular and fastest growing of all the Unix-like operating systems
  • Solaris – developed by Sun Microsystems for the SPARC platform and the most widely used proprietary flavor for web servers.

Below are steps to be followed to execute a query in the AIX Unix script.

  • Get the Server / DB details: Get the Server / Database details on which we want to execute our query. Usually, these are read from the script configuration file associated with every AIX script under each environment. Each environment has unique Server / Database details.
  • Connection Credentials: The Server / Database credentials are obtained from script config file and passed to script.
  • Isql untility: isql is a Unix utility to access server and database. Isql starts an interactive session if no options are specified. If no database is specified, users must connect to an existing database or create a new one after startingisql. It starts the interactive session by connecting to the named database, provided the login options are accurate and valid for the specified database. Depending on the options specified, isql starts an interactive or non-interactive session. General syntax of isql utility to execute a Sybase query is isql -user SYSDBA -password masterkey.
  • Error or Log Message: It is a general practice / advisable to code a separate step right after the step where Sybase query is executed in the Unix script using isql utility. This steps is usually coded using a unix command to write the error message coming the query. It also displays number of rows effected during the current run of the query in the previous step.

The process of executing the Sybase stored procedure from Unix script is almost same, but we need to take care of holding the return code Sybase stored procedure returns and use the return code to make decisions in the subsequent flow of the script.

We use exec command to execute the Sybase stored procedure in Unix Environment.

For example, to execute a Sybase stored procedure say db_sample,

We use

exec SP_RETURN = db_sample @param1 input, @param2 output.

 

Here SP_RETURN is the Unix variable declared to catch the stored procedure return code.

@param1 is the value that UNIX variable param1 holds that is passed as input to the stored procedure db_sample.

@param2 is the value that UNIX variable param2 holds that is passed as output to the stored procedure db_sample.

In the real legacy world, where the projects are implemented using COBOL / Sybase programs, using UNIX scripts to execute Sybase queries / Stored Procedures eliminates coding a new COBOL for any adhoc queries.