DB2

Db2 isolation level in bind

noreply@blogger.com (Gautam lata) — Sun, 05 Sep 2010 18:28:00 +0000

isolation level

An attribute that defines the degree to which an application process is isolated from other concurrently executing application processes.

uncommitted read (UR)

An isolation level that allows an application to access uncommitted changes of other transactions. The application does not lock other applications out of the row it is reading, unless the other application attempts to drop or alter the table.

cursor stability (CS)

An isolation level that locks any row accessed by a transaction of an application while the cursor is positioned on the row. The lock remains in effect until the next row is fetched or the transaction is terminated. If any data is changed in a row, the lock is held until the change is committed to the database.

read stability (RS)

An isolation level that locks only the rows that an application retrieves within a transaction. Read stability ensures that any qualifying row that is read during a transaction is not changed by other application processes until the transaction is completed, and that any row changed by another application process is not read until the change is committed by that process. Read stability allows more concurrency than repeatable read, and less than cursor stability.

repeatable read (RR)

An isolation level that locks all the rows in an application that are referenced within a transaction. When a program uses repeatable read protection, rows referenced by the program cannot be changed by other programs until the program ends the current transaction.

phantom row

A table row that can be read by application processes that are executing with any isolation level except repeatable read. When an application process issues the same query multiple times within a single unit of work, additional rows can appear between queries because of the data being inserted and committed by application processes that are running concurrently.

Can we better understand by the below table....

	UR	CS	RS	RR
Can the application see uncommitted changes made by other application processes?	Yes	No	No	No
Can the application update uncommitted changes made by other application processes?	No	No	No	No
*Can the re-execution of a statement be affected by other application processes? See phenomenon P3 (phantom) below.*	Yes	Yes	Yes	No
Can "updated" rows be updated by other application processes?	No	No	No	No
Can "updated" rows be read by other application processes that are running at an isolation level other than UR?	No	No	No	No
Can "updated" rows be read by other application processes that are running at the UR isolation level?	Yes	Yes	Yes	Yes
*Can "accessed" rows be updated by other application processes? See phenomenon P2 (nonrepeatable read) below.*	Yes	Yes	No	No
Can "accessed" rows be read by other application processes?	Yes	Yes	Yes	Yes
*Can "current" row be updated or deleted by other application processes? See phenomenon P1 (dirty-read) below.*	See Note below	See Note below	No	No

Examples of Phenomena:

Dirty Read. Unit of work UW1 modifies a row. Unit of work UW2 reads that row before UW1 performs a COMMIT. If UW1 then performs a ROLLBACK, UW2 has read a nonexistent row.

Nonrepeatable Read. Unit of work UW1 reads a row. Unit of work UW2 modifies that row and performs a COMMIT. If UW1 then re-reads the row, it might receive a modified value.

Phantom. Unit of work UW1 reads the set of n rows that satisfies some search condition. Unit of work UW2 then INSERTs one or more rows that satisfies the search condition. If UW1 then repeats the initial read with the same search condition, it obtains the original rows plus the inserted rows.

Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Mon, 23 Aug 2010 15:24:00 +0000

Collection Ideas

said that a `COLLECTION` is simply a way of grouping `PACKAGE`s into meaningful (for you) groups. You could use `COLLECTION`s to separate programs for different application areas, such as payroll and inventory. Another use might be to separate programs bound with `ISOLATION UR` from programs bound with `ISOLATION CS`. `COLLECTION`s are simply high-level grouping names to designate that this group of packages share something, anything, in common.

COLLECTIONs enable you to organize your PACKAGEs into like-kind groups. In DB2's younger days, with multiple DBRMs being bound into PLANs, all the DBRMs in a single PLAN had to be bound with the same BIND parameters. However, today you can BIND each PACKAGE into a COLLECTION that has a customized set of BIND parameters associated with it. An example would be to ISOLATE all programs using REOPT(VARS) in one COLLECTION and all programs using OPTHINT in another. DEGREE(ANY) is another BIND parameter that you may want to be a bit more vigilant in monitoring. An easy way of keeping an eye on programs (or children) is to put them in a room together. In other words, BIND parameters are much more granular today than they were when DB2 was young.

With the advent of BIND PACKAGE and the one-to-one relationship of a program to a package, we were given the ability to name the high-level qualifier for the tables accessed by the program. Therefore, the DBRM for one program could be bound into two different COLLECTIONs. The DBRM for program ABC123 could be bound into a COLLECTION called colcorp01, using corp01 as the table high-level qualifier. The same DBRM could be bound into a COLLECTION called colcorp02, using corp02 as the high-level qualifier. Or, you could BIND the same DBRM into colstress to run it against stress test tables and BIND it into colstress to run against regular test tables. Or, you could BIND a DBRM into a COLLECTION called colur to use when you access read-only decision support tables using ISOLATION UR and into a COLLECTION called colcs when you use active production data. There are dozens of examples. Just use your imagination.

So, at runtime, whichever approach you chose, you now have a PACKAGE with the exact same tattoo/timestamp/consistency-token in two different COLLECTIONs. How do you tell DB2 in which collection to search for Danny? Normally, DB2 would search through all of the COLLECTIONs in the named PLAN. But, if you want to search only one COLLECTION, you simply tell DB2 in your program. You can specify which COLLECTION to search by using an SQL statement called SET CURRENT PACKAGESET. PACKAGESET is simply a synonym for COLLECTION. Therefore, if you set the current PACKAGESET to colcorp01, you will access corp01's tables. If you set the current PACKAGESET to colcorp02, you will access corp02's tables. And the beauty of this is that you only have to maintain one program.

Versions

Suppose program A is changed and moved back into production. Before the program was changed it ran in 10 minutes and never bothered anyone. After the change, all the other programs running at the same time are experiencing `-911` timeouts. How do you fall back gracefully and rapidly to the prior version of the program?

At precompile time you can specify a VERSION ID. If the VERSION ID is the same as the current version, BIND will overlay the PACKAGE in its COLLECTION. But, if the VERSION ID is different from the current VERSION ID, BINDing the DBRM will produce a new PACKAGE that won't overlay the prior PACKAGE for the program. You'll have two PACKAGEs for the same program in the same COLLECTION. If you also move the current LOAD module with its old tattoo timestamp into a different loadlib (COBOL.BACKUP), the compile will not overlay it. Then, when you compile the modified source code, you'll have a LOAD module with the new tattoo/timestamp in the current loadlib. If you execute the new LOAD module, you'll find the new PACKAGE. If the system suffers, you can cancel the job and move the old LOAD module back into production simply by pointing to COBOL.BACKUP.

Copies

There are too many other nuances and possibilities to mention; however, one feature that may be useful is the ability to copy a package from one collection to another. If the statistics on your table vary greatly from daytime to evening or beginning of the month to end of the month, you can BIND a PACKAGE in a COLLECTION called colday or colbegin when the statistics in the CATALOG are representative of your daytime or beginning of the month table. You can then COPY that PACKAGE into another COLLECTION called colnight or colend when the statistics in the CATALOG are representative of your nighttime or end of the month table. COPY does a REBIND and uses the DBRM in the CATALOG as its input. Therefore, the tattoo/timestamp doesn't change. If you check the time of day or the day of month at the beginning of the program, you can SET CURRENT PACKAGESET to the appropriate COLLECTION for DB2 to search for Danny.

Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Thu, 19 Aug 2010 14:01:00 +0000

Packages and Collections

In V2R3, the DB2 developers solved the seven problems I listed (and many more) by introducing another layer in the program preparation procedure. The precompile step still split the program into tattooed twins, and the treatment of the modified source code stayed the same. But the DBRM could now be bound either into a PLAN (the old way) or into a PACKAGE. Although the relationship between a DBRM and a PLAN was one-to-many, the relationship between a DBRM and a PACKAGE was always one-to-one. Most of the work of BIND PLAN was moved into BIND PACKAGE. Therefore, if PGMA changed, only PACKAGE A would have to be bound.
If only one DBRM could be bound into a PACKAGE, but PGMA could still CALL PGMB, then a structure was needed to gather all of the PACKAGEs into a searchable list. This structure became a packagelist, rather than a memberlist, bound into a PLAN.

This tiny change solved many, but not all, of the problems inherent in the "memberlist of DBRMs bound into a PLAN" technique. To solve a few more problems, IBM introduced the concept of COLLECTIONs. A collection is simply a way of grouping packages into meaningful (for you) groups. You could BIND all of the packages that must run against Corporation 1's tables into one COLLECTION and all of the packages that must run against Corporation 2's tables into another. Or, you could use COLLECTIONs to separate programs for different application areas, such as payroll and inventory.

Another use might be to separate programs bound with ISOLATION UR from programs bound with ISOLATION CS. COLLECTIONs are simply high-level grouping names to designate that this group of packages share something, anything, in common.
So, now, with the introduction of COLLECTIONs, our BIND PLAN process (think search chain) can now include a packagelist of fully qualified package names such as COLLPAYROLL.PGMA, COLLPAYROLL.PGMB, and COLLCOMMON.PGMX. Or, if we choose, we can just substitute an asterisk for the program name and list (COLLPAYROLL.*, COLLCOMMON.*). Then any program bound into the COLLECTION will be accessible by the named PLAN.

Where Are Packages and Plans?

When you BIND a DBRM into a PLAN, or BIND a PACKAGE into a COLLECTION and then the COLLECTION(s) into a PLAN, the information must be stored somewhere safe inside DB2 until it's needed at run time. These items (PLANs and PACKAGEs) aren't stored in the DB2 Catalog. Rather, they're stored in the DB2 Directory. In fact, you can think of the Directory as the DB2 loadlib for the Danny portion of your program, complete with tattoo.

Run Time

At run time, the load module starts up and eventually hits a paragraph containing a CALL to DB2. This CALL contains information such as a description of the tattoo, the content of your SQL host variables (now populated), the statement number, and so on. The CALL invokes the COBOL-DB2 interface program, which connects to DB2. And if the run-time code necessary to execute your SQL isn't currently resident inside DB2 (in the EDMPOOL), we go to the buffer pool (BP0) assigned to the DB2 Directory and look there. If we don't find Danny there, we go to VSAM to disk to look in the COLLECTIONs named in the PLAN for the PACKAGE with the same name and the same tattoo, also known as the consistency token or timestamp.

And if you don't find the twin anywhere in DB2 (not that this has ever happened to anyone reading this column), you get a -805 error. If you're still using the older technique of binding DBRMs directly into PLANs via a memberlist, then an unsuccessful search for Danny will result in a -818 error code.

In my next column, I'll give you some ideas about the various ways of using the concept of the COLLECTION. I'll also write about VERSIONs and how they're used.

Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Sat, 14 Aug 2010 10:02:00 +0000

The DBRM (Danny)

At BIND time, DB2 created run-time executable instructions for the SQL portion of the program. But where are those instructions, and what are they called now that the term DBRM no longer applies?

The truth is, you have a choice. You can BIND the instructions for the SQL that was in the DBRM into a PLAN (the old way), or you can BIND the instructions into a PACKAGE (the not-so-old-but-no-longer-new way). The reason for this choice is historical. Back when knights were bold, dragons walked the Earth, and DB2 and some of us were young, DBRMs were bound directly into PLANs. In today's DB2 (since V2R3), there are two ways of doing BINDs. You may continue to BIND DBRMs into PLANs, or you may BIND DBRMs into PACKAGEs. With the second option, you keep your PLAN but use it only as a search chain. This column explains why things changed in V2R3. A future column will explain how things have continued to change in v.7 and v.8.

In the early releases of DB2, the DBRM (SQL originally embedded in our COBOL program but separated at precompile time into a PDS member) was bound into a PLAN (PLANA). This method worked just fine as long as the program was a standalone program. You coded the JCL to execute program PGMA naming the PLAN "PLANA," and at run time the twins found each other. However, things got a bit complicated when PGMA needed to CALL PGMB. Because only one PLAN could be named in an execute statement, the PLAN had to contain run-time instructions for both PGMA and PGMB. This problem was solved by having the BIND instruction for PLANA name a memberlist; the DBRMs for both PGMA and PGMB were listed as members. And if PGMB called PGMC, then the three would be listed as members. And if C called D, which could call E, F, G, or H, which could call I, J, K, L, or M, which could .... Well, you get the idea.
Memberlists got longer and longer. What were (and still are, if you cling to the old technique) the drawbacks of having a very long list?

Remember that DB2 authorization and SQL syntax are checked at BIND time. Access path alternatives are also weighed, the least-cost path is chosen (based on current statistics and system resources), and run-time instructions are created for the chosen path. Well, if the PLAN contains one member, A, this process should be quick. But what if there are 20 or 50 or 500 DBRMs in the memberlist? The BIND could take hours.
So, the PLAN, which took more than a while to BIND, contains 500 members. What if one of the programs, PGMA, changes? When the source code changes, the program must be precompiled. And precompile changes the tattoo and creates a new DBRM. That new DBRM must be bound into the PLAN. When the PLAN is bound, all 500 DBRMs (not just the modified PGMA) will be rebound. It could take hours to BIND a PLAN, even though 499 of the 500 programs haven't changed.
Also, remember that BIND is an opportunity to reassess and change the access paths for not only the modified program, but also every single program in the memberlist. If one program changes, every program in the list will go through BIND.
What if you want to modify PGMB to call a new program, PGMZ? You must not only precompile modified B and new Z, you also must add new PGMZ to the memberlist and BIND the whole list — all 501 DBRMs.
You want to remove PGMT? Edit the memberlist and then BIND the PLAN again with the remaining 500 members in the list and wait impatiently while DBRMs that haven't changed go through the BIND process.
Okay, modified program A turns your processor over on its back, casters up. You want to fall back to the original version of PGMA. And exactly how would you do that quickly? If (and it's a big if) you have the old DBRM with its old tattoo, you could move it into the DBRMlib and BIND the entire 500-member list (even though only A had regressed) and replace the new loadlib member A with the prior loadlib member A. Or, if you have the old source code for A, you could precompile it to recreate both the modified source code and the DBRM, and then COMPILE, LINK, and BIND, which would BIND all 500 DBRMs in the member list.
Program Q has to run against two sets of tables, one for Corporation 1 and a second for Corporation 2. The sets of tables have identical names but different high-level qualifiers. You could use synonyms, but they're unwieldy; binding during a time when a synonym points to the wrong set of tables could cause disaster.

These (and other) quandaries faced many DBAs in the days before V2R3 and the advent of PACKAGEs. what's is PACKAGE...... chek out next

Modified source+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Thu, 12 Aug 2010 09:59:00 +0000

till now , we had done a PRECOMPILE to separate our COBOL program into twins: Arnold (that's Governor Arnold now) as the modified source code (the SQL commented out and DCLGENs now INCLUDEd) and Danny as the DBRM containing the SQL that used to be in the source code. The COBOL source code (without any SQL) was compiled into run-time executable instructions for the COBOL portion of the program; DBRM Danny (all the SQL that used to be in the source module) went through BIND to generate run-time instructions for the SQL in our COBOL program.

but some question still running in mind ...like how Arnold, over in the COBOL loadlib, will find his long-lost twin — exactly what we were binding and where we would put it when we finished.

so ...study continue..

The Modified Source Code (Arnold)

At precompile time, when the SQL was stripped out of our program and moved into the DBRM (Danny) leaving only COBOL in the modified source code, you must have wondered how COBOL Arnold would ever find DB2 SQL Danny; in other words, how the COBOL would ever execute any SQL.

The explanation is simple. All of the executable SQL (and not all SQL is executable — a DECLARE CURSOR isn't, for example) in the COBOL program was replaced with a CALL statement (in our example, a COBOL CALL). The modified source code, complete with its calls to DB2 SQL Danny, was compiled and "linked" into a LOAD module. When this LOAD module is executed and hits a paragraph that once contained SQL (but now contains a CALL to Danny), there will be run-time executable code that knows how to link to a COBOL-DB2 interface module and connect to DB2, where it will find Danny and the run-time executable code for the SQL statement that previously had been in the paragraph.

Remember that we tattooed the twins? Well, this CALL contains the information DB2 needs to confirm not only that this LOAD MODULE is Arnold (complete with requisite tattoo), but also that it's the exact same Arnold that came out of the exact same precompile step as Danny. The CALL looks at the PLAN named in the execute statement of the job control language (JCL) and searches for the Danny out in DB2 with the same tattoo.

next post about most important member of this function called DBRM

Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Thu, 12 Aug 2010 09:53:00 +0000

ALL ABOUT BIND

BIND connects to the DB2 in which the program's LOAD module will run, reads the DBRM serially, and then performs three tasks.
The first of the BIND tasks is an authorization check. DB2 must make sure that the programmer has the BIND authority and the SQL authority to perform the requested SQL task (for example, updating the payroll master). When using standard authorization procedures, DB2 won't let you BIND a DBRM if you don't have the authority to execute the SQL that's in the DBRM. This is why you may have the authorization to BIND in development (accessing development tables) but don't have authorization to BIND in production, where the SQL accesses production tables. The second BIND task is a bit redundant. BIND, like precompile, must also check the syntax of the SQL, but the BIND check is more sophisticated. Instead of using the top, DECLARE TABLE portion of the DCLGEN, BIND uses the DB2 CATALOG table information to make sure that the column names are valid, that comparisons are numeric-to-numeric, and so on. This second syntax check occurs because you can't trust the one done by the precompiler because the precompiler check used the DCLGEN. You could have a DCLGEN and not have the DB2 table.

The third, and most important, BIND task is to come up with run-time instructions for the SQL in the DBRM. Each SQL statement is parsed and all of the possible (realistic) methods for retrieving the desired columns and rows from the table are weighed, measured, and evaluated based on possible estimated I/O, CPU, and SORT overhead. A ton of information is used as input to the BIND process, not just CATALOG information put there by running the RUNSTATS utility. BIND input includes, for example:

Indexes (what columns are in the indexes?)
Columns (how long is this column and how much room will it occupy in a SORT record?)
System resources (how big are the system resources, buffer pool, and RIDPOOL?)
Processors (how big are they and how many engines do they have?)
DB2 (what release is running?)
Parameters (what are the values of the BIND parameters?)

After all that input (and more) is weighed and compared, the cheapest, most costeffective access path is chosen, and the runtime instructions for that one path are created. (Interestingly, DB2 BIND sometimes generates instructions for more than one path.) This process is called optimization, and it's repeated for each SQL statement in the DBRM until all access paths are decided and the run-time instructions are created for each. As the optimizer decides on each path, writes are done to DB2.

BIND checks to see if you bound with the parameter EXPLAIN(YES); if so, it writes documentary evidence about the chosen path to the PLAN_TABLE and to the DSN_STATEMNT_TABLE for your edification.
BIND also writes a lot of information to multiple CATALOG tables, documenting the fact that the BIND did occur. In fact, the tattooed DBRM, which is not used at run time, is moved into the CATALOG. Objects chosen by the optimizer are documented in the CATALOG in cross-reference tables. And BIND parameters are recorded in the CATALOG also.

WHERE ARE THE INSTRUCTIONS?

It's interesting that the actual instructions for the access path are not written to the CATALOG. You can't look at information in the DB2 CATALOG to figure out whether a query will do synchronous or asynchronous reads at run time. You can't tell if the query will match on three columns of an index or five. The actual run-time instructions aren't stored in the CATALOG. They're definitely not in the DBRM, which is input to the BIND. So, where are they stored?
This question is one of the many reasons that this column grew into two parts. Where in the heck are the run-time instructions? Should you ever use the PLAN_TABLE at run time? Are the run-time instructions in a package, a plan, or a version? All of the above? The infamous "it depends"? Where are the instructions stored while you wait for the LOAD module to run? Like many question come in mind.... so don't worry ....and
Stay tuned for answers to these and other questions..........................

Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

noreply@blogger.com (Gautam lata) — Wed, 11 Aug 2010 17:07:00 +0000

No matter how long programmers have worked with DB2 for z/OS and OS/390 , they still ask the difference between a plan and a package — and what in the heck a collection is. I planned to write post on this topic so i also remember it...... one by one ....

THE PRECOMPILER

The DB2 Precompiler does not need DB2 to run. It carries out three primary tasks as it reads the program serially, top-to-bottom, looking for DB2 delimiters.
First, if the delimiters surrounded an INCLUDE statement, the Precompiler goes to the INCLUDE library named in the job control language data definition statement and pulls the included MEMBERNAME into the program. This function is the same as a COBOL COPY MEMBERNAME, but the timing is different. COBOL COPYBOOKs get copied in at COMPILE time; DB2 INCLUDEs get copied in at precompile time. The only difference between an SQL INCLUDE and a COBOL COPY is timing. The most common item INCLUDEd in a program was (and is) a DCLGEN. DCLGENs are structures that describe a table. One DCLGEN is usually included for each table that the program will access at run time. Each DCLGEN is a two-part structure consisting of a DECLARE TABLE statement, which describes the table in DB2 SQL language, and a COBOL structure that describes the table using an 01-Level COBOL working storage structure (much like a typical copybook for a VSAM file).

Second, if the delimiters surround an SQL statement, the precompiler does a very basic syntax check to make sure that the column and table names are valid (that they're spelled correctly and that the columns and the table exist). Many DBAs and programmers think that this validation is done by reading the DB2 CATALOG, but they're wrong. Remember, the precompiler doesn't need DB2 or its CATALOG. DB2 might not even be installed on the machine. The DB2 Precompiler uses the top part of the DCLGEN to validate the SQL syntax.

The third, and most important, task performed by the DB2 Precompiler is to split the program into two parts: a COBOL and a DB2 part. All of the SQL that the programmer carefully embedded is stripped out of the program and put into its own partitioned data set (PDS) member, called a DBRM. A single program containing two languages, COBOL and SQL, goes into the DB2 Precompiler and two pieces come out. Twins, but fraternal twins — much like Arnold Schwarzenegger and Danny DeVito. Arnold looks just like his COBOL mother, and Danny looks just like his DB2 father. COBOL Arnold, with all of the SQL commented out, goes down one path in life. SQL Danny, containing only SQL, goes down a different path in life.

The twins, separated at birth, have a tendency to lose each other. To help the twins find each other later in life (in other words, at run time), the precompiler engraves each with identical tattoos. The tattoo is carried forward with COBOL Arnold, through compile and link edit, into the LOAD module in the LOAD library. The tattoo is part of the run-time executable code of the LOAD module. The same tattoo is carried forward with SQL Danny through BIND. BIND is to SQL what COMPILE is to COBOL. The purpose of COBOL COMPILE is to come up with run-time code for the COBOL. The purpose of BIND is to come up with run-time executable code for the SQL.

Both sets of code bear identical tattoos (timestamps or consistency tokens).
So, the COBOL twin becomes a transportable load module in the COBOL LOADLIB and the SQL becomes a transportable DBRM in the DBRMLIB. Just as the COBOL twin had to be compiled, the DBRM twin has to go through BIND to create the run-time executable code for the DB2 portion of the COBOL program and put that executable code into the "right" DB2 subsystem.

Using FTP to Download Files from the IBM Mainframe to a PC

noreply@blogger.com (Gautam lata) — Mon, 09 Aug 2010 12:22:00 +0000

To download files from your IBM mainframe account to a PC, you must first make sure that the files are transferable. Some of your files may be stored on the IBM mainframe's minidisk as A0 mode. In order to transfer them, you must change the mode to A1. The instructions below explain how to check your files, change them all to A1 mode, and then transfer them to a PC.
Preparing Your Files to be Downloaded:
1. Log in to your account on the IBM mainframe. For instructions on how to log in, refer to:

Accessing the IBM Mainframe with a PC
- or -
Accessing the IBM Mainframe with a Macintosh

2. Make sure the file you wish to copy is in your account by typing at the Ready prompt: filelist
You will see a list of your files and the minidisk on which they are stored.
[Optional: If you wish to view a file, type browse in the CMD column in front of the filename. When you have finished viewing the file, press F3 to return to the filelist.]
3. Press F3 to return to the Ready prompt.
4. Then to change all your files to A1 mode, at the Ready prompt type:
rename * * A0 = = A1
5. Log out of your account by typing logout at the Ready prompt.

Downloading Your Files
1. On the PC, create a folder to store the files you will download from the IBM mainframe.
2. Click on the Start button and select Run.
3. In the Open box, type: ftp vm.temple.edu and press Enter.
4. At the User prompt, enter your IBM mainframe username and press Enter.
5. At the Password prompt, enter your IBM mainframe password and press Enter.
Note: Once you access FTP (File Transfer Protocol), you can type help at the ftp> prompt to see a list of FTP commands. For a short description of a command's function, type help and the command such as help get.
6. At the ftp> prompt, type:
lcd folder
where folder is the full path of the folder that will store your IBM mainframe files. For example, if you created a folder called vmfiles on the Windows desktop, you would type:
lcd c:\windows\desktop\vmfiles
Then press Enter.
7. Optional: If you want to see a list of files in the drive you specified, type dir. Note that you can scroll up and down to view all the files in your account.
8. If you are not downloading a SAS transport file, continue to the next step. Otherwise, if you are downloading a SAS transport file, type binary.
Note: After you download a file as binary, you can return to the default setting (ascii text) by typing ascii.
9. You can now download individual files or groups of files from your IBM mainframe account to the folder you specified by using one of the following commands below.
Downloading a single file:
To download a single file, type:
get filename.filetype
For example, you might type get report.sas or get myfile.spss or get all.notebook depending on the name of your file. Note that there is a period between the filename and filetype.
Downloading multiple files:
To download more than one file, use the wildcard (*) character and the mget command. The wildcard character tells the system to transfer any file with the filetype specified in the command.
For example, if you have several files with the filetype or extension sas, you can transfer all of them by typing:
mget *.sas
Note: The mget command will prompt you to confirm the transfer of each file. If you want to turn off interactive prompting, type prompt and press Enter before issuing the mget command. To return to interactive prompting, just retype prompt.
10. To quit FTP, type quit and then press Enter.

SQLCA

noreply@blogger.com (Gautam lata) — Sun, 08 Aug 2010 17:51:00 +0000

Contents of SQLCA:

01 SQLCA.
       05 SQLCAID PIC X(8).
       05 SQLCABC PIC S9(9) COMP.
       05 SQLCODE PIC S9(9) COMP.
       05 SQLERRM.
              49 SQLERRML PIC S9(4) COMP.
              49 SQLERRMC PIC X(70).
      05 SQLERRP PIC X(8).
      05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP.
      05 SQLWARN.
              10 SQLWARN0 PIC X(1).
              10 SQLWARN1 PIC X(1).
              10 SQLWARN2 PIC X(1).
              10 SQLWARN3 PIC X(1).
              10 SQLWARN4 PIC X(1).
              10 SQLWARN5 PIC X(1).
              10 SQLWARN6 PIC X(1).
              10 SQLWARN7 PIC X(1).
     05 SQLEXT.
             10 SQLWARN8 PIC X(1).
             10 SQLWARN9 PIC X(1).
             10 SQLWARNA PIC X(1).
             10 SQLSTATE PIC X(5).

The SQLCA is described in the following table, including the field names used in each language, the data type, and a discussion of the purpose of each field.

SQLCA :- Description

COBOL Name	C Name	PL/I Name	Data Type	Purpose
SQLCAID	sqlcaid	SQLCAID	char(8)	A character string (SQLCA) that identifies the beginning of the SQLCA.
SQLCABC	sqlcabc	SQLCABC	integer	Specifies the length of the SQLCA. Value is always 136.
SQLCODE	sqlcode	SQLCODE	integer	Contains a return code pertaining to the most recent SQL statement executed. Important values are: 0 Statement executed successfully (may have had warnings). >0 Executed successfully but with an exception condition. <0 An error occurred. 100 End of data reached.
SQLERRML	sqlerrml	SQLERRML	smallint	The length of the SQLERRMC string (0 to 70). If 0, the value of SQLERRMC is not pertinent.
SQLERRMC	sqlerrmc	SQLERRMC	varchar (70)	One or more tokens, separated by x'ff', that are substituted into error messages to provide specific information about an error condition.
SQLERRP	sqlerrp	SQLERRP	char(8)	Indicates the product signature in the format pppvvrrm where: ppp = XDB vv = XDB version number rr = XDB release number m = Operating system: N = NT O = OS/2 D = DOS
SQLERRD(1)	sqlerrd[0]	SQLERRD(1)	integer	Reserved for internal use.
SQLERRD(2)	sqlerrd[1]	SQLERRD(2)	integer	Reserved for internal use.
SQLERRD(3)	sqlerrd[2]	SQLERRD(3)	integer	Number of rows affected by an INSERT, UPDATE or DELETE command.
SQLERRD(4)	sqlerrd[3]	SQLERRD(4)	integer	Not used by XDB.
SQLERRD(5)	sqlerrd[4]	SQLERRD(5)	integer	Contains the position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE statement.
SQLERRD(6)	sqlerrd[5]	SQLERRD(6)	integer	Not used by XDB.
SQLWARN0	sqlwarn0	SQLWARN0	char(1)	Flag used to signal warning condition.If blank,then all other SQLWARN variables are blank. If value is W, then at least one other SQLWARN has a W.
SQLWARN1	sqlwarn1	SQLWARN1	char(1)	If value is W, then at least one column's value was truncated when stored in a host variable.
SQLWARN2	sqlwarn2	SQLWARN2	char(1)	If value is W,then at least one null value was eliminated from the argument of a column function.May not be set to W for a MIN function because its result are not dependent on the elimination of null values.
SQLWARN3	sqlwarn3	SQLWARN3	char(1)	If value is W,then the number of columns is larger than the number of host variables.
SQLWARN4	sqlwarn4	SQLWARN4	char(1)	If value is W,then a prepared UPDATE or DELETE statement does not contain a WHERE clause.
SQLWARN5	sqlwarn5	SQLWARN5	char(1)	If value is W,your program tried to create an index that contains a field that exceeds the maximum length for an index. This use is XDB-specific.
SQLWARN6	sqlwarn6	SQLWARN6	char(1)	Not used by XDB.
SQLWARN7	sqlwarn7	SQLWARN7	char(1)	Not used by XDB.
SQLWARN8	sqlwarn8	SQLWARN8	char(1)	Not used by XDB.
SQLWARN9	sqlwarn9	SQLWARN9	char(1)	Not used by XDB.
SQLWARNA	sqlwarna	SQLWARNA	char(1)	Not used by XDB.
SQLSTATE	sqlstate	SQLSTATE	char(5)	Contains SQLSTATE value for this error.

SQL CODES
Successful Execution SQLCODE
000 SUCCESSFUL EXECUTION

Warning SQLCODES
+100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE
+162 TABLE SPACE database-name, tablespace-name HAS BEEN PLACED IN CHECK PENDING
+304 A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2
+402 LOCATION location IS UNKNOWN
+403 THE LOCAL OBJECT REFERENCED BY THE CREATE ALIAS STATEMENT DOES NOT EXIST
+558 THE WITH GRANT OPTION IS IGNORED BECAUSE GRANT IS TO PUBLIC
+560 THE WITH GRANT OPTION IS IGNORED FOR UPDATE (COLUMN-LIST)
+561 THE ALTER AND INDEX PRIVILEGES CANNOT BE GRANTED TO “PUBLIC AT ALL LOCATIONS”
+625 THE DEFINITION OF TABLE table-name HAS BEEN CHANGED TO INCOMPLETE
+802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number.

For error code check my next post......

Locking in DB2

noreply@blogger.com (Gautam lata) — Sun, 08 Aug 2010 14:40:00 +0000

Locking and concurrency issues can have a significant impact on the performance of a DB2 application. A good understanding of how the various serialization mechanisms work in DB2 will help application developers to take informed design decisions. Happy Reading!

Locking & Lock Management

Locking and lock management are a must in any multithread environment that uses a shared, updateable database, so that all users access the same version at any one time. Without proper locking, application processes that update would have to be single-threaded, thereby reducing throughput and increasing response time. Running batch processes concurrently with online processes would be nearly impossible.

Locking is required to allow inter-process concurrency and avoid data integrity problems. Locking is therefore a fundamental and implicit system service provided by a database management system (DBMS). Two or more independent application processes must be prevented from simultaneously accessing and updating specific data occurrences, whether the occurrences be called rows as in DB2, segments as in information management system (IMS), or logical records as for the virtual storage access method (VSAM).

Locking is a partnership between the DBMS which provides concurrency and consistency and the application that drives granularity and locking. Everything that the DBMS can “guarantee”, the application can “undo” with a combination of caching data across commits, failure to check return codes, or inadequate and incorrect restart logic.

Locking Modes

DB2 takes locks on table spaces, tables (if table space is segmented), and pages or rows. The three locking modes are:

Share – The lock owner and any concurrent process can read, but not change, the locked DB2 object. Other concurrent processes may acquire Share or Update locks on the DB2 object. Also called S-lock.

Update – The lock owner can read the DB2 object and intends to change it. Concurrent processes may acquire Share locks and read the DB2 object but no other process can acquire an Update lock. Update locks must be promoted to Exclusive locks before DB2 actually changes the DB2 object. Also called U-lock.

Exclusive – Only the lock owner can read or change the locked data, with exceptions like concurrent applications using uncommitted read isolation. Also called X-lock.

Basic reasons for Locking

Reason 1 - Losing updated data

Without concurrency control, two processes, A and B, might both read the same row from the database, and both calculate new values for one of its columns, based on what they read. If A updates the row with its new value, and then B updates the same row, A's update is lost.

With locking, if the application uses updatable cursors (i.e., with ‘for update of’ clause), DB2 grants a U-lock to user A. User B requests for U-lock and will wait till user A releases the lock (either commits or rolls back). If updatable cursors are not used, then DB2 grants an S-lock to User A and consequently grants an S-lock to User B. When User A and User B try to update the column value, they get into a deadlock situation. When a deadlock exists, DB2 decides whether User A or User B should be rolled back.

Reason 2 - Reading uncommitted data

Also without concurrency control, process A might update a value in the database, and process B might read that value before it was committed. Then, if A's value is not later committed, but backed out, B's calculations are based on uncommitted (and presumably incorrect) data.

With locking, DB2 grants a X-lock to user A. User B tries to get a S-lock and will have to wait till user A commits or rolls back the change.

Reason 3 - Unrepeatable read within a unit of work

User A wants to read the same data twice. It is essential that another user has not updated the data between the two read processes.

Let us look at the following example:

SELECT * FROM EMP

WHERE SALARY >

(SELECT AVG(SALARY) FROM EMP)

This SQL statement reads the EMP (employee) table twice: once to find the average salary and next to find all rows with salary greater than the average. Without any locking between the two read processes, another user can update the EMP table between the two read processes. This update can lead to a wrong result for user A.

With DB2 locking, the following are the options the application developer can choose from depending on the need for concurrency:

1. Use the package or plan isolation level repeatable read (RR), or use the WITH RR clause in the SQL SELECT statement.

2. Lock the table in share or exclusive mode explicitly, although exclusive mode is an overkill in this particular case.

3. Mark the table space Read-only. Option is not recommended.

Effects of Locking

Lock Suspension

An application process is suspended when it requests a lock that is already held by another application process and cannot be shared. The suspended process temporarily stops running. For example, using an application for inventory control, two users attempt to reduce the quantity on hand of the same item at the same time. The two lock requests are queued. The second request in the queue is suspended and waits until the first request releases its lock. Promotion to Update lock to Exclusive lock may cause a suspension if other processes are holding Share locks.

The suspended process resumes running when:

All processes that hold the conflicting lock release it.

The requesting process times out or deadlocks and the process resumes to deal with an error condition.

Timeout

An application process is said to time out when it is terminated because it has been suspended for longer than a preset interval. COMMIT and ROLLBACK operations do not time out.

Deadlock

A deadlock occurs when two or more application processes each hold locks on resources that the others need and without which they cannot proceed.

Jobs EMPLJCHG and PROJNCHG are two transactions.

1.Job EMPLJCHG requests a lock for page A of table N while holding the lock on page B of table M. The job is suspended, because job PROJNCHG is holding an exclusive lock on page A.

2.Job PROJNCHG requests a lock for page B of table M while holding the lock on page A of table N. The job is suspended, because job EMPLJCHG is holding an exclusive lock on page B.

The situation is a deadlock. DB2 scans for deadlocked processes at regular intervals according to the value set as DEADLOCK TIME during installation. DB2 can roll back the current unit of work for one of the processes or request a process to terminate. That frees the locks and allows the remaining processes to continue.

It is possible for two processes to be running on separate DB2 subsystems, each trying to access a resource at the other location. In that case, neither subsystem can detect that the two processes are in deadlock; the situation is resolved only when one process times out.

Lock sizes

For DB2 objects, we have the LOCKSIZE option on the CREATE TABLESPACE and ALTER TABLESPACE statements. The choices are ANY, ROW, PAGE, TABLE, or TABLESPACE, with ANY as the default.

ANY permits DB2 to make the final choice and DB2 favors page locking as a good compromise between high concurrency and high CPU consumption. Most often, DB2 implicitly locks at the page level, but certain SQL DML statements may be such that DB2 has the option to use table or table-space level or even row locking as an alternative.

PAGE is a good design default to allow for interprocess concurrency with read and write intent against a table.

Row locking can be considered to improve concurrency still further (reduce suspensions, timeouts) but it may increase locking cost for sequential update processing and introduce more opportunities for deadlocks.

The TABLE or TABLESPACE option should rarely be used in an online environment that emphasizes shared data. The explicit SQL statement LOCK TABLE is another option available. TABLE or TABLESPACE options could be useful where the data is read-only; a single user or an update process requires exclusive control over the table. Taking one lock on the entire table or table space saves the CPU overhead of locking individual pages as they are accessed. The more pages accessed by the process, the greater the savings.

Duration of Lock

The duration of a lock is defined as the length of time a lock is held and varies according to the type of lock.

If a page or row is locked, DB2 acquires the lock only when it is needed (i.e., when individual pages or rows are accessed). When the lock is released depends on whether the data is accessed for read-only or for update and on the isolation level specified, but it is rarely held beyond the next commit point.

Partition, table, and table space locks can be acquired when a plan is first allocated, or you can delay acquiring them until the resource they lock is first used. They can be released at the next commit point or be held until the program terminates.

Effects of Lock

Effects of locking depend upon the size and duration of the locks. For maximum concurrency, locks on a small amount of data held for a short duration are better than locks on a large amount of data held for a long duration. However, acquiring a lock requires processor time, and holding a lock requires storage; thus, acquiring and holding one table space lock is more economical than acquiring and holding many page locks. So it is essentially a trade-off between performance and concurrency.

Lock Escalation

Lock escalation is the releasing of all the page or row locks that a process holds on a single table or table space in order to acquire a table or table space lock instead. Lock escalation balances concurrency with performance by using page or row locks while a process accesses relatively few pages or rows, then changing to table space or table locks when the process accesses many.

LOCKMAX is an option on the CREATE TABLESPACE and ALTER TABLESPACE statements. It defines the maximum number of page or row locks an application process can hold simultaneously in the table space. If a program requests more than that number, locks are escalated.

Objects of Lock

The object of a lock is the resource being locked.
User data in target tables - A target table is a table that is accessed specifically in an SQL statement, either by name or through a view. Locks on those tables are the most common concern, and the ones over which you have most control.

User data in related tables - Operations subject to referential constraints can require locks on related tables. For example, if you delete from a parent table, DB2 might delete rows from the dependent table as well. In that case, DB2 locks data in the dependent table as well as in the parent table. If the application uses triggers, any triggered SQL statements can cause additional locks to be acquired.

DB2 internal objects – Locks on the following internal tables: Portions of the DB2 catalog, the skeleton cursor table (SKCT) representing an application plan, the skeleton package table (SKPT) representing a package and the database descriptor (DBD) representing a DB2 database.

Indexes and data-only locking

DB2 Version 4 introduced a new type of index (type 2 index) that eliminate locks on index pages so that your applications can avoid deadlock and timeout problems on the index. No index page locks are acquired during processing. Instead, DB2 uses a technique called data-only locking to serialize changes. Index page latches are acquired to serialize changes within a page and guarantee that the page is physically consistent. Acquiring page latches ensures that transactions accessing the same index page concurrently do not see the page in a partially changed state.

The data locks can be held for a long duration such as until commit. However, the page latches are only held for a short duration while the transaction is accessing the page. Because the index pages are not locked, hot spot insert scenarios (which involve several transactions trying to insert different entries into the same index page at the same time) do not cause contention problems in the index.

A query that uses index-only access might lock the data page or row, and that lock can contend with other processes that lock the data. However, using lock avoidance techniques can reduce the contention.

Lock Avoidance

Lock avoidance is a mechanism used under certain circumstances by DB2 to increase concurrency, at the same time avoiding calls to IRLM to request a page or row lock.

In a lock-avoidance situation, DB2 reads a page without first taking an IRLM lock; hence, the page might contain uncommitted data. To prevent retrieval of uncommitted data, DB2 uses a combination of mechanisms:

Page latching controlled by DB2 is a very efficient way to ensure physical consistency of the page, just as locks ensure that data is committed.

Commit Log Sequence Number (CLSN) to check for the committed state at a page level - The page oriented CLSN technique takes advantage of the page log relative byte address (last change RBA) in the header of every page. If the page log relative byte address value of the page is less than the CLSN for the page set, all of the rows in the page are in a committed state and no lock is required to ensure the logical consistency of the data in the page.

Possibly UNCommitted (PUNC) a deeper checking at a row level, done only if the CLSN check fails – Row-oriented PUNC technique checks the PUNC mark that indicates if an individual row in a page is committed.

Users have no direct control over the use of lock avoidance. Read-only SQL can use lock avoidance techniques to avoid locking. Other lock avoidance or reduction techniques are use of CURRENTDATA(NO), ACQUIRE(USE), RELEASE(DEALLOCATE), isolation, lock size and frequent commits.

Tips for Writing High-performance SQL

noreply@blogger.com (Gautam lata) — Thu, 22 Nov 2007 05:17:00 +0000

Tips for Writing High-performance SQL
These tips apply broadly when writing high-performance stored procedures. Unfortunately, unlike some tips, you can't simply apply most of them without first considering the nature and schema of the data you're querying.

Avoid using cursors (as well as other looping structures) as much as possible.
Cursors are inefficient, and database engines usually don't have the best loop implementations in terms of performance.
On the database side, you can usually replace code involving cursors with aggregate SQL statements (SELECT, INSERT, and UPDATE) that use vector tables. All database engines are heavily optimized for aggregate statements, so even if a loop is unavoidable, it is always better to execute a few aggregate statements in a loop with a small number of iterations, than to create a cursor and execute simple statements over a large number of iterations.
Even if initial performance tests, especially with a small amount of data, show cursors to be more efficient than a complex aggregate statement, it is worthwhile to try to optimize the operation by breaking it into smaller portions or using other approaches—unless you can guarantee that the data value will stay small. Cursor approaches will not scale.

Filter data wisely.
One alternative to using cursors uses a fall-through approach, filtering and aggregating data in multiple steps via a set of data storages, which could be physical tables, temporary tables, or table variables. It is usually best to include some aggregate filters into aggregate statements to filter out the majority of data in one simple shot whenever necessary, working on smaller amounts of data. Then you can proceed with joining and filtering, making sure to keep the number of join permutations under control at all times.

It is usually more efficient to execute multiple statements with one condition than a single statement with multiple OR conditions…
… when executing UPDATE and DELETE statements against permanent database tables that can be accessed by multiple users simultaneously. This tip is especially important from the scalability point of view; from the performance point of view the difference is usually marginal. The major reason for the tip is the locking of the database records and the lock escalations that occur behind the scenes.
Make wise distinctions between temp tables and table variables.
Table variables are in-memory structures that may work from 2-100 times faster than temp tables. But keep in mind that access to table variables gets slower as the volume of data they contain grows. At some point, table variables will overflow the available memory and that kills the performance. Therefore, use table variables only when their data content is guaranteed not to grow unpredictably; the breaking size is around several thousand records. For larger data volumes, I recommend temp tables with clustered indexes. Interestingly, I've found that a temp table with one clustered index is often faster than having multiple simple indexes. In contrast, multiple simple indexes with physical tables are often faster than one clustered index.

Make careful distinctions between hard rules and assumptions.
This is more of a business design tip, which applies more to code design than to performance and scalability design in general. In real life however, performance and scalability are generally the first things to suffer from improper design. When rules are implemented as assumptions, they usually cause unnecessary calculations to be performed, affecting performance. However, when assumptions are implemented as rules they tend to cause errors and algorithm failures, which usually requires an urgent redesign. That, in turn, is usually performed with business constraints and results in inefficient final algorithms. That's because bad design decisions are often corrected in a rush and without sufficient resources—sometimes under pressure from customers whose businesses are usually in a critical stage when problems are uncovered, but must continue operating during the process.

Pay attention to join order.
Using proper join order sometimes lets the database engine generate hints that execute joins with an optimal amount of records. Most database engines also support hard hints, but in most cases you should avoid using hard hints and let the database engine figure out the best way to do its job on its own.

Be careful when joining complex views…
…to other views and database tables in complex SELECT statements. When the database contains a significant amount of data, SQL Server engine tends to recalculate the execution plan of the resulting statement, which often results in an inefficient execution plan and may kill the performance. The most difficult part is that the behavior of SQL Server engine is inconsistent in that respect, and heavily depends on the database size, indexes, foreign keys, and other database structures and constraints. The consistent work-around is to pre-select data from the view into a temp table with the reasonable pre-filters, and then use that temp table in place of the underlying view.

Create indexes on temp tables wisely.
As mentioned in an earlier tip, clustered indexes are usually the best in terms of performance for temp tables; however, there is a difference between creating the index before or after inserting data into the temp table. Creating the index before the insert complicates the insert, because the database engine must order the selection. For complex selections such as those mentioned in Tip 7, the extra ordering may overcomplicate the overall statement and drastically degrade the performance. On the other hand, creating the index after the insert forces the database engine to recalculate the execution plan of the stored procedure every time it is called. Therefore, the decision is always a trade-off and you should make it based on the relative costs of the two possibilities.

In general, try to avoid execution plan recalculation.
One common cause of recalculation occurs when the stored procedure contains several paths that depend on values passed in parameters. However, whether avoiding recalculation is possible depends on the complexity of the stored procedure and on other circumstances, such as those described in tip 8. When the engine does recalculate execution, performance always suffers; however, recalculating the execution plan of the caller does not force the execution plan recalculation of the called procedure (or view or function). Therefore, the workaround is to divide one stored procedure into multiple procedures (depending on the passed-in parameters), and then call the children from the parent conditionally. You should perform this subdivision very carefully though, because it can be a maintenance nightmare—but sometimes it seems to be the only way to achieve acceptable database performance and scalability.

mainframe tips list

noreply@blogger.com (Gautam lata) — Wed, 10 Oct 2007 09:39:00 +0000

Wk. 39/2007: Submitting jobs for parallel execution
Wk. 37/2007: IBM mainframe abbreviations
Wk. 35/2007: ISPF EDIT command COMP (compare)
Wk. 33/2007: Segmented tablespaces
Wk. 31/2007: A simple calculator
Wk. 27/2007: The difference between correlated and noncorrelated subselects
Wk. 25/2007: Execution of TSO batch programs
Wk. 23/2007: OPTIMIZE FOR n ROWS in SQL statements
Wk. 21/2007: TSO commands in COBOL and PL/1
Wk. 19/2007: A trip with FTP
Wk. 17/2007: SQL function VALUE (COALESCE)
Wk. 14/2007: Comparisons in REXX
Wk. 12/2007: Optimizing your OUTER JOINS
Wk. 10/2007: Your own TSO command processor
Wk. 08/2007: Using EXISTS in SQL
Wk. 06/2007: More about deleting datasets
Wk. 04/2007: ISPF EDIT commands COPY, CREATE and REPLACE
Wk. 02/2007: Minimising the timeout risk when performing a DB2 INSERT
Wk. 51/2006: Variable DD names in PL/1
Wk. 49/2006: Reasonable use of ISPF KEYLIST
Wk. 47/2006: Validation using a DB2 trigger
Wk. 45/2006: Messages from ISPF services
Wk. 43/2006: Correlated subselects for versioning
Wk. 41/2006: Faster EDIT macroes
Wk. 39/2006: Assignment of DCB informations in PL/1
Wk. 37/2006: Problems using newcopy in CICS
Wk. 35/2006: Names of DB2 subsystems using data sharing
Wk. 33/2006: Dataset delete
Wk. 31/2006: Reuse of a DB2 image copy
Wk. 27/2006: TRANSMIT and RECEIVE of PO datasets
Wk. 25/2006: Smarter global temporary tables in DB2
Wk. 23/2006: Enlargement of a concatenation with one more dataset
Wk. 21/2006: Avoid SAVE of data in ISPF EDIT
Wk. 19/2006: The LIBDEF function in ISPF
Wk. 17/2006: Calling LOAD modules from REXX
Wk. 15/2006: Spaces in SQL
Wk. 13/2006: Record splitting using SORT
Wk. 11/2006: Informations in HSM
Wk. 09/2006: SQL statements with joins, GROUP BY and HAVING
Wk. 07/2006: More SMS compression
Wk. 05/2006: Generic searches in ISPF
Wk. 03/2006: Join of columns with different definitions
Wk. 01/2006: Please help me out of this
Wk. 50/2005: The TRANSLATE function in REXX and PL/1
Wk. 48/2005: Calling CICS programs from batch or TSO
Wk. 46/2005: Testing return codes in batch TSO
Wk. 44/2005: -DIS UTIL and DIAGNOSE DISPLAY SYSUTIL
Wk. 42/2005: A job step that always gets executed
Wk. 40/2005: Who is using my MQ queue
Wk. 38/2005: Global temporary tables in DB2
Wk. 36/2005: Start BookManager using your own bookshelf
Wk. 34/2005: Dataset copying using ISPF
Wk. 29/2005: IBM mainframe wildcards
Wk. 27/2005: Options SORTKEYS, SORTDEVT and SORTNUM for DB2 LOAD/REORG
Wk. 25/2005: Making your own HSM backup
Wk. 23/2005: Adjusting Dataset List defaults
Wk. 21/2005: The FIND ALL command
Wk. 19/2005: The HSM command TSO HSEND Q ACT
Wk. 17/2005: Making your own enqueue/dequeue mechanism in DB2
Wk. 15/2005: Writing to a local queue on another MQ subsystem
Wk. 12/2005: Optimizing the SELECT list in SQL statements
Wk. 10/2005: Controlling your COBOL modules using DD cards
Wk. 08/2005: Making less MQ remote queue definitions
Wk. 06/2005: Remove leading blanks using PL/1
Wk. 04/2005: CICS In-Storage tables
Wk. 02/2005: Making your MQ definitions in batch
Wk. 52/2004: IN lists in SQL statements
Wk. 50/2004: ISPF EDIT command HEX
Wk. 48/2004: Accessing main storage with PL/1
Wk. 46/2004: Line commands for ISPF Edit macros
Wk. 44/2004: Object oriented PL/1
Wk. 42/2004: SELECT * FROM table
Wk. 40/2004: DISTINCT used with UNION or UNION ALL
Wk. 38/2004: Using SYSOUT=* to display dataset contens
Wk. 36/2004: CURRENT SQLID
Wk. 34/2004: Install of CICS definitions without using CEDA
Wk. 32/2004: User friendly BookManager
Wk. 28/2004: Returning bad SQLCODE when SELECTing zero rows
Wk. 26/2004: Dynamic call of programs from PL/1
Wk. 24/2004: Fallback of DB2 plans/packages
Wk. 22/2004: The OPC RECOVER function
Wk. 20/2004: WORK datasets for ISPF
Wk. 18/2004: Selfdocumenting REXX programs
Wk. 16/2004: SMS dataset compression
Wk. 14/2004: The DISCARD option on DB2 REORG
Wk. 12/2004: The ISPF EDIT command BNDS
Wk. 10/2004: The TSO command PROFILE
Wk. 08/2004: Dirty reads in SQL statements
Wk. 05/2004: Command SE in SDSF and JCL parameter SCHENV
Wk. 03/2004: Executing DFSORT using the DSN processor
Wk. 52/2003: Deleting all rows in a DB2 table
Wk. 50/2003: Issuing ISPF messages from REXX
Wk. 48/2003: The order of predicates in SQL
Wk. 46/2003: Hypertext with ISPF Edit/View
Wk. 44/2003: Deletion of members
Wk. 42/2003: Index-screening in DB2
Wk. 40/2003: Changing data using File-Aid/Batch
Wk. 38/2003: Executing other SQL statements than SELECT using DSNTIAUL
Wk. 36/2003: Changing your data using DFSORT
Wk. 34/2003: Speeding up deletion of rows in DB2 tables
Wk. 32/2003: Read any file using PL/1
Wk. 29/2003: Row level locking in DB2
Wk. 27/2003: Trapping of screen output from TSO commands
Wk. 25/2003: Allocation of DB2-spaces
Wk. 23/2003: Using % in front of TSO commands
Wk. 21/2003: Showing the contens of an ISPF-table
Wk. 19/2003: Disabling Abend-Aid in batch
Wk. 16/2003: Optimization of calls to VAG from COBOL programs
Wk. 14/2003: Finding rows in DB2 table with the same key
Wk. 12/2003: Concatenation on DD *
Wk. 10/2003: DB2 trace facilities: DSN-command and CAF
Wk. 08/2003: Asynchronous tasks in CICS: DELAY
Wk. 06/2003: Asynchronous tasks in CICS: START and CANCEL
Wk. 04/2003: ISPF EDIT labels .ZF and .ZL
Wk. 02/2003: Display Active (DA) in SDSF of many MVS-systems
Wk. 50/2002: Communicate with another CICS from your CICS
Wk. 48/2002: Cheating the DB2-optimizer with an IN-list
Wk. 46/2002: Special validations in ISPF panels
Wk. 44/2002: DB2 CAF interface
Wk. 42/2002: Names for variables in REXX
Wk. 40/2002: Errorhandling in CICS
Wk. 38/2002: DB2 REORG UNLOAD EXTERNAL
Wk. 36/2002: Dynamic allocation of REXX-libraries
Wk. 34/2002: Generating ready-to-use SYSIN with DB2
Wk. 32/2002: Using ? in DB2 SQL EXPLAIN statements
Wk. 28/2002: Copying the same line to different places with ISPF EDIT
Wk. 26/2002: ISPF log dataset
Wk. 24/2002: Accessing main storage with COBOL
Wk. 22/2002: Restore of HSM backups
Wk. 20/2002: A fast DB2 reorg
Wk. 18/2002: CICS-programs in status REQ
Wk. 16/2002: The ISPF command RETP
Wk. 13/2002: Setting return code to 0 in TSO batch
Wk. 11/2002: GROUP BY on expressions in SQL
Wk. 09/2002: Who is using my dataset
Wk. 07/2002: The SQL CASE expression
Wk. 05/2002: Finding informations about COBOL abends
Wk. 03/2002: Browse and Edit of DB2 tables
Wk. 52/2001: Giving other users access to your datasets
Wk. 50/2001: The PROJECT, GROUP and TYPE fields in ISPF
Wk. 48/2001: Making your own bookshelf in BookManager
Wk. 46/2001: An ISPF-command trick
Wk. 44/2001: The table with exactly one row
Wk. 42/2001: Internal tables in REXX
Wk. 40/2001: Establishing the existance of a row using SQL
Wk. 38/2001: The MAX-RC column in SDSF
Wk. 36/2001: The CUT-function in the 3270-emulator
Wk. 34/2001: Crossreferences between DB2-tables and programs
Wk. 32/2001: Syncronizing jobs with DISP=OLD
Wk. 28/2001: UNLOAD your DB2-data with DSNTIAUL
Wk. 26/2001: LOAD module timestamp
Wk. 24/2001: ISPF tool ISRDDN with enqueues
Wk. 22/2001: Easy copying of datasets
Wk. 20/2001: Finding data inconsistency using SQL
Wk. 18/2001: Some useful SDSF commands
Wk. 15/2001: When a dataset runs out of space
Wk. 13/2001: Useful RACF commands
Wk. 11/2001: TSO command HQ
Wk. 09/2001: ISPF View command REPLACE
Wk. 07/2001: Conversion of CHAR and VARCHAR to DECIMAL using SQL
Wk. 05/2001: Use of SPACE parameter with existing datasets
Wk. 03/2001: ISPF Edit line-commands L and F
Wk. 01/2001: ISPF Edit macro ONLY

what is DB2

noreply@blogger.com (Gautam lata) — Mon, 08 Oct 2007 11:28:00 +0000

A {relational database} from {IBM}. When running under IBM's MVS ({Multiple Virtual Storage}) {operating system}, DB2 is implemented on top of {VSAM} and uses its underlying data structures. DB2, later called "DB2 Universal DataBase", also runs under {windows NT}, {AIX}, {Solaris} and, most recently, {Linux}

DB2 is a family of relational database management system (RDBMS) products from IBM that serve a number of different operating system platforms. According to IBM, DB2 leads in terms of database market share and performance. Although DB2 products are offered for Unix-based systems and personal computer operating systems, DB2 trails Oracle's database products in UNIX-based systems and Microsoft's Access in Windows systems.

In addition to its offerings for the mainframe OS/390 and VM operating systems and its mid-range AS/400 systems, IBM offers DB2 products for a cross-platform spectrum that includes UNIX-based Linux, HP-UX, Sun Solaris, and SCO UnixWare; and for its personal computer OS/2 operating system as well as for Microsoft's Windows 2000 and earlier systems. DB2 databases can be accessed from any application program by using Microsoft's Open Database Connectivity (ODBC) interface, the Java Database Connectivity (JDBC) interface, or a CORBA interface broker.

On January 30th, 2006, IBM released a no-charge edition of DB2 called DB2 9 Express-C. This was an expected response to the recently announced free versions of Oracle 10g and Microsoft SQL Server. Express-C will have no limit on number of users or database size. It is deployable on Windows and Linux machines with up to two processors (4 cores in total) and up to 4GB of memory.

DB2 can be administered from either the command-line or a GUI. The command-line interface requires more knowledge of the product but can be more easily scripted and automated. The GUI is a multi-platform Java client that contains a variety of wizards suitable for novice users. DB2 supports both SQL and XQuery. DB2 has native implementation of XML data storage, where XML data is stored as XML(not as relational data or CLOB data) for faster access using XQuery.