Q. Can Anyone Tell the Exact Query for Retrieving the Structure of a Table

DB2

Q:.When I do a runstats on a table, does DB2 for OS/390 automatically do a runstats
on its indexes?
A: No, this sometimes can cause DB2 to provide an inefficient access path. Make
sure to include the parameter index in your runstats statement. For example:
RUNSTATS mydb.mytspace TABLE(ALL) INDEX(ALL) SHRLEVEL REFERENCE
Q: A plan has many DBRMs and packages. When I execute a program using the command:
RUN PROG(p1) PLAN(mypln), how does DB2 know which package or DBRM to execute?
A: Before executing the plan mypln, you must have bound into it DBRMs and
packages related to this program. Let's assume that when you precompiled your
program p1, you created a DBRM named p1 (you could not choose a different name).
Let us also assume that you have issued the following bind commands:
BIND PACKAGE (u1) MEMBER(p1) LIBRARY(l1)
BIND PACKAGE (u2) MEMBER(p1) LIBRARY(l1)
BIND PLAN(mypln) MEMBER(p1, p2) LIBRARY(l1) PKLIST(u1.p1, u2.p1, u3.p3)
where l1 is the library where DBRM p1 resides, p2 is another DBRM, and p3 is
another package.
In reality, you probably would seldom bind the same DBRM p1 repeatedly, however,
in case you want to use different bind options for the same program, this is the
way to do it.
Element p1 exists in plan mypln as DBRM p1 and packages u1.p1 and u2.p1. When
your program p1 executes and DB2 encounters the first SQL statement, DB2 will
determine which one of the three elements in mypln to execute by checking the
contents of special register, current packageset. If it is empty, DB2 will
search DBRMs and packages in the plan in a specific order, depending on whether
the plan executed at a local or remote location. This whole searching scheme is
complicated.
Q: How do I register a stored procedure in the SYSIBM.SYSPROCEDURES catalog table
on a S/390 database server?
A: The "CREATE PROCEDURE" command is the only command that can be used to
register a stored procedure in the database catalogs. Depending on the level of
the MVS operating system, the procedure information goes into either the
SYSIBM.SYSPROCEDURES catalog table (DB2 for S/390 version 5 or earlier servers)
or to the SYSIBM.SYSROUTINES catalog table (DB2 for S/390 version 6 or higher
servers

Q. can anyone tell the exact query for retrieving the structure of a table.

A. SELECT * FROM SYSIBM.SYSTABLE WHERE TBNAME="tablename"

Q. Explain Scrollable Cursors
A. Probably the most significant new application development enhancement made
to DB2 V7 is scrollable cursors. A scrollable cursor provides the ability to scroll forward and backward through the data once the cursor is open. This can be achieved using nothing but SQL - no host language code (e.g., COBOL,C) is required to facilitate a scrollable cursor in DB2 V7. A scrollable cursor makes navigating through SQL result sets much easier. There are two
types of DB2 scrollable cursors:
SENSITIVE -- updateable; can access data changed by the user or other users
INSENSITIVE -- not updateable; will not show changes made
To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7. Declared temporary tables are discussed later in the section on data management. DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor.
Scrollable cursors allow developers to move through the results of a query in multiple ways. The following key words are supported when fetching data from a scrollable cursor:
NEXT - will FETCH the next row, the same way that the pre-V7 FETCH statement functioned
PRIOR - will FETCH the previous row
FIRST - will FETCH the first row in the results set
LAST - will FETCH the last row in the results set
CURRENT - will re-FETCH the current row from the result set
BEFORE - positions the cursor before the first row of the results set
AFTER - positions the cursor after the last row of the results set
ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set
RELATIVE n - will FETCH the row that is n rows away from the last row fetched
For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive or a negative number, and it can be represented as a numeric constant or as a host variable.
All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data. For example, consider the following cursor logic:
DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT FIRSTNAME, LASTNME
FROM DSN8710.EMP
ORDER BY LASTNME;
OPEN csr1;
FETCH LAST csr1 INTO :FN, :LN;
Issuing this SQL will declare a scrollable cursor named csr1, open that cursor, and then FETCH the last row from the cursor's results set. The FETCH LAST statement will reposition the cursor to the last row of the results set, and then FETCH the results into the host variables as specified.
Scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.
But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do
so.
Limiting the Number of Rows Fetched
Application developers frequently need to retrieve a limited number of qualifying rows from a table. For example, maybe you need to list the top ten best selling items from inventory, or a list of the top five most expensive products (i.e., highest price tag). There are several ways to
accomplish this prior to DB2 V7 using SQL, but they are not necessarily efficient.
The first reaction is to simply use the WHERE clause to eliminate non-qualifying rows. But this is simplistic, and often is not sufficient to produce the results desired in an optimal manner. What if the program only requires that the top ten results be returned? This can be a somewhat
difficult request to formulate using SQL alone.
Consider, for example, an application that needs to retrieve only the top ten most highly paid employees from the EMP sample table. You could simply issue a SQL request that retrieves all of the employees in order by salary,but only use the first ten retrieved. That is easy; for example:
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC;
You must specify the ORDER BY clause with the DESC key word. This sorts the results into descending order, instead of the default, which is ascending. Without the DESC key word, the "top ten" would be at the very end of the results set, not at the beginning.
But that does not really satisfy the requirement - retrieving only the top ten. It merely sorts the results into descending sequence. So the results would still be all employees in the table, but in the correct order so you can view the "top ten" salaries very easily. The ideal solution should
return only the top ten employees with the highest salary and not merely a sorted list of all employees.
You can code some "tricky" SQL to support this request for all versions of DB2, such as the following:
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM DSN8710.EMP A
WHERE 10 > (SELECT COUNT(*)
FROM DSN8710.EMP A
WHERE A.SALARY < B.SALARY)
AND SALARY IS NOT NULL
ORDER BY SALARY DESC;
This SQL is portable from version to version of DB2 (as well as to another DBMS, such as Oracle or SQL Server). And, of course, you can change the constant 10 to any number you wish, thereby retrieving the top 20, or top 5, as deemed necessary by the needs of your application.
Since the SALARY column is nullable in the EMP table, you must remove the nulls from the results set. And the ORDER BY is required to sort the results in the right order. If it is removed from the query, the results will still contain the top ten, but they will be in no particular order.
DB2 V7 provides an easier and less complicated way to limit the results of a
SELECT statement - the FIRST key word. You can code FETCH FIRST n ROWS
which will limit the number of rows that are fetched and returned by a SELECT
statement.
Additionally, you can specify a new clause -- FETCH FIRST ROW ONLY clause on SELECT INTO statements when the query can return more than one row in the answer set. Doing so informs DB2 to ignore the other rows.
There is one difference between the new V7 formulation and the other SELECT statement we reviewed, and that is the way "ties" are handled. A tie occurs when more than one row contains the same value. The previous query we examined may return more than 10 rows if there are multiple rows with the same value for price within the top ten.
Using the FIRST key word DB2 will limit the number of rows returned to ten,even if there are other rows with the same value for price as the number ten row in the results set. The needs of your application will dictate whether ties are to be ignored or included in the result set. If all "ties" need to be included in the results set, the new V7 feature may not prove to be helpful.

Q I have a table (say Table1) with only one row.I need to select this row and process and do some activities in my program. This activities may take some time depending upon the data.Once finished update this table(Table1) with the new values. But I need to lock this table exclusively till my entire operation is completed. Since I know my table has only one row I

don't want to use a cursor..

Can anyone provide a solution...?

A. Two things come to mind. First, you could use 'select for update'. In this case, DB2 will lock the table till you update, or free your resources (I prefer this method). Second, you could make your program into a stored procedure. This wouldn't place an explicit lock, but would ensure that the whole program is completed as 1 unit of work.

Q. Could anyone briefly describe when the timestamp mismatch occurs (-818)..and how it has to be rectified?Starting from (Precompiler,compiler,link-editor,bind)---how does the -818 occurs and which step does it occurs and wot shd be done to rectify that abend?

A. When binding being is done the load module's and the DBRM's timestamps should match. Otherwise u will get an abend.

To rectify this u have to compile (which ofcourse includes precompilation step) and create the load module and DBRM again(this time the timestamps will match). Later u have to bind and run.

Q. ExplainINNER AND OUTER JOINS?

A. Inner Join:

combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with all the values in corresponding columns of the other table or tables. This kind of join which involve a match in both

columns are called inner joins.

Outer join :

Is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

Q. I need to read the input file and based on the input field(say

account number) , I should retrieve the rows from the db2 table and the

retrieved rows needs be updated. Mind you this account number varies for each and every record..

A. Do the following:

1) Declare cursor C1 for select * from table where ac_no = :hv-acc-no

2) Open the file and Read the record, get the a/c number = file-acc-no, Until EOF

3) Move file-acc-no to hv-acc-noand open cursor C1

4) Fetch C1 , Until SQLCODE = 100 (No more rows)

5) Update table set db2_name= :file-name where CURRENT OF C1

6) once SQLCODE of 100 is reached then goto step2 and read next record.

7) Close cursor C1

8) Close file

Q. I just want to know whether we can limit the output of a particular query (in batch or as spufi) to a particular value. Say the following query retrieves 1000 rows.

SELECT * FROM TABLE A;

I want the output rows of this query to be limited to 100.We have ROWCOUNT in ORACLE. Just want to know whether any similar one is available in DB2.

A. You can use

SELECT * FROM TABLE A

FETCH FIRST 100 ROWS ONLY;

Or

SELECT * FROM TABLE A

FETCH LAST 100 ROWS ONLY;

to fetch the rows.

Q. can anyone help me in counting the no. of columns in a given table.

A. GIVE SQL STMT LIKE THIS

SELECT COUNT(*) FROM SYSIBM.SYSCOLUMNS WHERE

TBCREATER='USER-ID' AND TBNAME='UR-TABLE-NAME'

Q. I need to run a sum function on a table,it is taking tremendous amount of time

e.g.

SELECT SUM(FIELDNAME)

FROM TABLE

WHERE GROUP_NO= 7833

AND SUBGRP_NO =T00

Is there any way I can optimize this query?

A. Build an index on those 2 columns. If this is not feasible or an index already exists on those 2

columns, then consider an external Sort/Sum through Syncsort.

Q. I'm doing the following steps in my program

1. reading the data from the flat file.

2. and inserting the data into db2 table

My problem is the date value from the flat file is like 00/00/0000. I cant insert these in to my db2 tables I have also tried inserting spaces in to date field of db2 table. That also not worked out

A.The date stored in db2 tables in the following format YYYY-MM-DD ( “-“ or “/” depends on DB2 installation).You can solve your problem by re arranging the data.

05 WS-DATE-YYYYMMDD.

10 WS-DATE-YYYY PIC 9(04)

10 FILLER PIC X(01) VALUE ‘-‘

10 WS-DATE-MM PIC 9(02)

10 FILLER PIC X(01) VALUE ‘-‘

10 WS-DATE-DD PIC 9(02)

05 WS-DATE1-MMDDYYYY.

10 WS-DATE1-MM PIC 9(02)

10 FILLER PIC X(01)

10 WS-DATE1-DD PIC 9(02)

10 FILLER PIC X(01)

10 WS-DATE1-YYYY PIC 9(04)

MOVE FILE-REC TO WS-DATE1-MMDDYYYY.

MOVE WS-DATE1-YYYY TO WS-DATE-YYYY

MOVE WS-DATE1-MM TO WS-DATE-MM

MOVE WS-DATE1-DD TO WS-DATE-DD

Now if you load it to DB2, it will work

Q. I have a DB2 table with a primary key. Is it necessary to declare a index while creating the table?

A. When a primary key is defined in an SQL CREATE TABLE statement, the table is marked unavailable until the primary index is created by the user. So you need to create a unique index on primary key. The unique index on a primary key is called primary index.

When a primary key is defined in an SQL ALTER TABLE statement, a unique index must already exist on the columns of that primary key. This unique index is designated as the primary index.

Q. can some tell me how to do auto increment of primary key in db2 and oracle

A. For Auto increment you can use sequences in Oracle...... The command goes like this

CREATE SEQUENCE <Sequence name>
START WITH 1000
INCREMENT BY 1
minvalue 0
maxvalue 100

Once a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn (which returns the current value of the sequence) or the NEXTVAL pseudocolumn (which Increments the sequence and returns the new value).

Q. what is meant by sqlda in db2..what is the relationship between sqlda and hostvariable?

what types of error we can get very frequently by using this

A. SQLDA stands for SQL Descriptor Area. It is used among other things to store the description (or layout) of result sets returned from stored procedures. I havent't really used the SQLDA much at all so hopefully there will be more people to respond. As for hostvariable, there is no specific
relationship to SQLDA. Host variables in COBOL at least are preceded by a ":" as in :Host-variable-1 pic x(10). When a host varaible is used in an SQL statement that is when the ":" colon is used as in exec sql select date_column from ... into :xxx-start-date.

Q. Does it will be committed automatically when I isssue the folowing command without an explicit commit?
EXEC SQL
CLose tablename
End-exec

A.I think in the question it is Close Cursor name insted of tablename. Ifat the system level (i.e) atDB2 installation Auto commitoption is selected thenit commits automatically without explicit commit or otherwise with thecomplete execution of your program commits the data.(It is also one way of commit)

Q. How to delete duplicate records from DB2 table?

A. Here is the Query for deleting duplicate records
DELETE *
FROM Employee AS A
WHERE A.ID > (SELECT MIN(B.ID)
FROM Employee B
WHERE A.Empno = B.Empno);
ID is the primary key (ROWID) and Empno is the key on which u want to eliminate duplicates.
ROWID is a unique key for every record. I think it should be a number. The ROWID Field is present in all the Databases (not sure about this). Try using ROWID instead of ID in the above query.

Q. How to get the details of all processes utilizing a resource?

A. I hope this partially satisfies your requirements ...... This query would give the list of packages which make use of a particular table.
SELECT DISTINCT (A.NAME)
FROM SYSIBM.SYSPACKAGE A,
SYSIBM.SYSPACKSTMT B
WHERE A.LOCATION = B.LOCATION AND
A.COLLID = B.COLLID AND
A.NAME = B.NAME AND
A.CONTOKEN = B.CONTOKEN AND
B.STMT LIKE '%<TABLENAME>%'
This query would give the list of plans which make use of a particular table.
SELECT A.NAME
FROM SYSIBM.SYSPLAN A,
SYSIBM.SYSDBRM B,
SYSIBM.SYSSTMT C
WHERE A.NAME = B.PLNAME AND
B.NAME = C.NAME AND
B.NAME = C.PLNAME AND
C.TEXT LIKE '%<TABLENAME>%'