Uncategorized

SQuirreL SQL Client-How to Install,Configure and Connect to DB2

Hi Friends,

I was thinking of accessing mainframe database by using some freeware tool to run basic queries without using the licensed hummingbird software and here I got one, Squirrel SQL Client. I installed it in my system and made necessary configuration to talk to mainframe db2 LPC tables.

SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.

This document will demonstrate how install the client and configure it to connect to and work with a DB2 database. The steps for configuring the SQuirreL SQL Client to work with any other database management system will be very similar.

Prerequisites
The SQuirreL SQL Client is a Java application, and as such, it requires a Java 6 runtime environment (JRE) which can be downloaded from Oracle. 
Once the Java 6 runtime environment is installed, the install jar for the SQuirreL SQL Client can be found here: http://www.squirrelsql.org/#installation.

Double-click on it to launch the default application i.e JAVA “jar” file; the installer will display a welcome dialog as shown in Figure 1.

Click the “Next” button again, and the dialog prompts for a path to the location where the application should be installed.
 

Click the “Next” button to continue the installation. The dialog now presents a list of “packs” that can be installed, which include required software as well as optional plug-ins.

A plug-in is usually a piece of software that can be added (or plugged in) to the application to extend its capabilities. The SQuirreL SQL Client installer ships database-specific plug-ins for each of the major databases which allow the application to work optimally with the corresponding database.
The “Base” pack is the only one required for installation. However, the “Standard” pack is a group of plug-ins that is optional, but highly recommended and pre-selected.

Now that the SQuirreL SQL Client application is installed, we will launch it and configure it to connect to a LPC DB2 DEV database.
 
 
Now we can configure the DB2 HIT driver in the SQuirreL SQL Client. In the main window, there are two buttons on the far left-side of the application entitled “Aliases” and “Drivers”. Click on the “Drivers” button and the drivers panel will appear. This panel contains templates for each of the database product that is known to have a JDBC driver that can be loaded into the SQuirreL SQL Client .

Click on driver tab and below screen will pop up. Select the squirrel-sql.jar file as Java access class path. Select the driver from drop down of Class path as shown below.

 

Click on Extra Class Path tab and below screen will pop up as shown below.

 
Select the hitjdbcdb2-3.59.jar file from HIT Driver folder as Extra class path. Select the driver from drop down of Class path as shown below

 
 
Here  I have used the URL as
jdbc:db2://10.5.5.11:4002;rdbname=GBLDB2D;package_collection_id=NULLID;catalog_qualifier = ODBAD

Please note this URL is same we used for CDP(Class Data Pump) for accessing mainframe DB2 table through IMR/CAS. And my LPC user Id and password to connect to dev db2 table through thin layer using HIT Driver.

 
Click on Test Button to test the connection of Squirrel SQL client with Db2 database.
 

 
Now that we have connected to the database, click on the SQL tab to access the SQL script editor. The following SQL statements should be typed into the script editor, ensuring that there is one line of white space between each statement and the next. Here, I have used to query the fetch first 100 rows from claim detail table from Dev Environment and was able to retrieve it.
 
This has been a short introduction to installing the SQuirreL SQL Client and connecting to a DB2 database. If anybody has questions or any specific problems on using the above Squirrel SQL client please let me know. Also if anybody has used this free tool before in a different way to connect to some other database, please share your understandings.

Questions/Suggestions/Comments are always welcome.

Thanks
Som

Uncategorized

Build records without a program

I am following various mainframe forums and I find this interesting utility IEBDG-Test Data generator. I did not know about this utility for long. Sometimes we had to build millions of records to volume test an application. I always hate to write a program as it takes time to construct and test it (still might have some errors). I looked for an Utility that could build records without any input and IEBDG helped me out.

To illustrate, below is an example code that builds 100 different records without any input

Uncategorized

BW REPORTING AREA Concepts

1. What are the Query Tuning you do when you use reporting?
a) Install BW Statistics and use of aggregates for reporting

b) Avoid using too many characteristics in rows and columns, instead place it in free characteristics and navigate / drill-down later.

Uncategorized

MODELING AREA Concepts

1. What is data integrity?
Data Integrity is about eliminating duplicate entries in the database. Data integrity means no duplicate data.

2. What is the difference between SAP BW 3.0B and SAP BW 3.1C, 3.5?
The best answer here is Business Content. There is additional Business Content provided with BW 3.1C that wasn't found in BW 3.0B. SAP has a pretty decent reference library on their Web site that documents that additional objects found with 3.1C.

Uncategorized

MFS CONTROL BLOCK CONCEPT

 

MFS CONTROL BLOCK CONCEPT:

DIF

Device Input Format

Description of data for input display

DOF

Device Output Format

Description of data for output display

MID

Message Input Descriptor

Description of program's I/O area for input

MOD

Message Output Descriptor

Description of program's I/O area for output

Uncategorized

TSO delete via Cyber fusion

You all might have used FTP(file transfer protocol) to send/ receive files,setting of flags in other server and even deleting the files from other server .Cyber fusion is also one of the secured ,multi protocol,next generation transfer management technique to send information from one server to another sever with encrypted and compressed data .It uses lot of encryption algorithm like Blowfish 56 bit or Blowfish Long 448 bit encryption.

Uncategorized

VSAM Error Processing

The way VSAM handles error conditions , many I/O errors that cause COBOL program to abend  when we use non-VSAM access method do not cause an abend when we use VSAM.Instead the file status is being used to indicate the nature of the problem.

Uncategorized

ZEKE U7SVC Concepts

There are lot of scheduling tools like CA-7 , CA-11, Control M and ZEKE where the jobs are scheduled to run at particular time in Mainframe. ZEKE scheduling tool doesn’t have development version and it is used to schedule the job in production environment.If you code the command STEPNAME EXEC U7SVC in the JCL and want to test the JCL in development environment ,it will trigger the job in production environment provided that the the job which you are testing in develpoment already have a copy in production.

Uncategorized

VSAM Concepts

VSAMVSAM stands for Virtual Storage Access Method. As the name implies, it is a method by which different type of datasets can be accessed quickly and efficiently. It is used to organize, store, catalog, retrieve and delete datasets. The access method will work only in these two conditions: • DATA • Method of getting the data To understand VSAM, the following things everyone should know: • We need to see how data is organized in VSAM dataset • We need to understand how to use the utility that are available in AMS. • We need to know how to use the power of VSAM in COBOL.Advantages of VSAM: • Data can be accessed faster because of the way VSAM dataset is organized. • Records are physically deleted from disk (instead of being logically removed). • Records can be inserted in more efficient manner.

Disadvantages of VSAM: • VSAM data sets require more storage space than other type of datasets. This is because VSAM datasets carry control information in them, in addition to actual data that they are comprised of.

DATA ORGANISATION IN VSAM DATASETS: VSAM supports four types of file organizations: • Key Sequenced Data Sets (KSDS) • Entry Sequenced Data sets (ESDS) • Relative Record Data Sets (RRDS) • Linear Data Sets (LDS) The efficiency with which the data is accessed depends upon the data that is organized in these VSAM files.

Concepts: VSAM was, by several accounts, intended to replace all of the earlier data management systems in use by IBM’s operating systems. Conventional (non-VSAM) access methods generally provide only a single type of dataset organization. VSAM provides three: Key Sequenced Data Set (KSDS), where each record is identified for access by specifying its key value – a sequence of characters embedded in each data record which uniquely identify that record from all other records in the dataset. KSDS datasets are similar to Indexed Sequential Access Method (ISAM) datasets, with many of the same characteristics, but also having distinct advantages over ISAM. Entry Sequenced Data Set (ESDS), where each record is identified for access by specifying its physical location – the byte address of the first data byte of each record in relationship to the beginning of the dataset. ESDS datasets are similar to Basic Sequential Access Methid (BSAM) or Queued Sequential Access Method (QSAM) datasets. Relative Record Data Set (RRDS), where each record is identified for access by specifying its record number – the sequence number relative to the first record in the dataset. RRDS datasets are similar to Basic Direct Access Method (BDAM) datasets. VSAM datasets are frequently referred to as clusters. A KSDS cluster consists of two physical parts, an index component, and a data component. ESDS and RRDS clusters consist of only a single component, the data component.

KSDS Cluster Components Each record in the data component of a KSDS cluster contains a key field, which must be the same number of characters and occur in the same relative position in each record. The records are stored in the data component in logical sequence based upon their key field value. The index component of the KSDS cluster contains the list of key values for the records in the cluster with pointers to the corresponding records in the data component. The records in a KSDS may be accessed sequentially, in order by key value, or directly, by supplying the key value of the desired record. The records of a KSDS cluster may be fixed length or variable length. Records may be added or deleted at any point within a KSDS cluster, and the affected record is inserted or removed, and the surrounding records will be reorganized as required to maintain the correct logical sequence.

ESDS Cluster Components The records in an ESDS cluster are stored in the order in which they are entered into the dataset. Each record is referenced by its relative byte address (RBA). In an ESDS dataset of 100 byte records, the RBA of the first record is 0, the RBA of the second record is 100, the RBA of the third record is 200, etc. The records in an ESDS may be accessed sequentially, in order by RBA value, or directly, by supplying the RBA of the desired record. The records of an ESDS cluster may be fixed length or variable length. Records may not be deleted from an ESDS cluster, and they may only be added (appended) to the end of the dataset, following records previously written.

RRDS Cluster Components The records in an RRDS cluster are stored in fixed length slots. Each record is referenced by the number of its slot, which is a number varying from 1 to the maximum number of records which may be contained in the dataset. The records in an RRDS cluster may be accessed sequentially, in relative record number order, or directly, by supplying the relative record number of the desired record. The records of an RRDS cluster must be of fixed length. Records may be added to an RRDS cluster by writing a new record’s data into an empty slot, and records may be deleted from an RRDS cluster, thereby leaving an empty slot where the record that was deleted was previously stored.

Control Intervals In non-VSAM data management methods, the unit of data that is moved between memory and the storage device is defined by the block. In VSAM, the unit of data that is transferred in each physical I/O operation is defined as a control interval. A control interval contains records, control information, and (in the case of KSDS clusters) possibly free space which may later be used to contain inserted records. When a VSAM dataset is loaded, control intervals are created and records are written into them. With KSDS clusters, the entire control interval is usually not filled. Some percentage of free space is left available for expansion. With ESDS clusters, each control interval is completely filled before records are written into the next control interval in sequence. With RRDS clusters, control intervals are filled with fixed-length slots, each containing either an active record or a dummy record. Slots containing dummy records are available for use when new records are added to the dataset.

Control Areas Control intervals are grouped together into control areas. The rules used for filling and writing control areas are similar to those which apply for control intervals. For ESDS and RRDS clusters, control areas are filled with control intervals that contain records. For KSDS clusters, some of the control intervals in each control area may consist entirely of free space that can be used for dataset expansion.

VSAM Catalogs When a non-VSAM dataset is created, the user has the option, by means of the DISP=(,CATLG) JCL entry, of creating a catalog entry for the dataset. The catalog keeps track of the unit and volume on which the dataset resides and can be used for later retrieval of the dataset. With VSAM datasets, creation of a catalog entry to record the unit and volume, as well as many other characteristics of the dataset, is not optional. Prior to VSAM, catalog entries for non-VSAM datasets were contained in OS CVOLS (operating system control volumes). VSAM maintains its own catalog, which is itself a KSDS cluster, into which catalog entries describing VSAM clusters are recorded. The same VSAM catalog may also be used to contain the catalog entries for non-VSAM datasets.

VSAM ERROR PROCESSING: Because the way VSAM handles error condions, many I/O errors that cause the COBOL program to abend whne you use non VSAM access methods don't cause an abend when you use VSAM.  

File Status Code OPEN CLOSE READ WRITE REWRITE DELETE SATART Recommended action
0 File successfully opened File successfully closed File successfully read File successfully written File successfully rewritten File successfully deleted Successful Completion Continue processing
2     Vaild duplicate alternate key Vaild duplicate alternate key Vaild duplicate alternate key     Continue processing
10     End of file reached         Normal AT END processing
21       Record out of sequence (sequential access only)       Print error message and continue
22       Duplicate key       Print error message and continue
23     Record no found     Record no found Specified key not found Print error message and continue
24       No more Space allocated to file       Terminate the job
30 Uncorrectable I/O error Uncorrectable I/O error Uncorrectable I/O error Uncorrectable I/O error Uncorrectable I/O error Uncorrectable I/O error Uncorrectable I/O error Terminate the job
90 Unusable file;possibly an empty opened as INPUT as I-O VSAM logic error VSAM logic error VSAM logic error VSAM logic error VSAM logic error VSAM logic error Terminate the job
91 Password Failure             Terminate the job
92 File already opened File not open File not open or end of file already reached File not open ; incorrect key for EXTEND file File no open;no previous READ File no open;no previous READ(sequential access) Invalid request;probably file not open Terminate the job
93 Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Not enough storage for VSAM task, or file contention problem Terminate the job
95 Conflicting the attributes             Terminate the job
96 No DD or DLBL statement             Terminate the job
97 File not closed by previous job             Terminate the job