Answer / nirmal khatri

Hi

We can do it by SQL also, below is SQL which is working on
iSeries(AS/400)

insert into nk11581/temp1 (select * from nk11581/temp1
where rrn(temp1) in(
SELECT distinct dec((rrn(temp1)/2)) FROM nk11581/temp1
))

Answer / reddy kanupuru

Hi,

Here i am giving the procedure , how to make the table into
half..... using program... I dont know whether any direct
SQL command is there or not. This is just for ur idea what
i know.

1) Find the total number of rows in a table using count(*)

2) Declare two variable in working storage section.
eg: 02 A pic 9(4) .
02 B pic 9(2).

store the count no into one variable .assume u stored that
in A.

then divide that by 2. so u will get half of the rows in a
table. strore that value in B.

3) Take another variable name C .

eg: 02 c pic 9(4) value 0.

perform para1 until B =C

para1:

Retrive the row from the table.

move to corresponding host variables ( use table to store
muliple rows ).

insert into table(u can insert multiple rows at a time ...)

do the same for storing the other half into the another
table.

means here 1 st half rows were stored in one temp
table .... the other in the one table.

Answer / pavani

i forgot to mention the c value increment in the above
mail.increment the c value after each row retrieved.

Answer / sachin

ADDING TO ans1
no need to write perform

direct use below query

select * from table fech first :b rows only.

Answer / arpita

We can achieve this by using STRSQL and CPYF commands as
follows -

Type STRSQL, Press Shift + F1, option1 Change session
attributes.Type 3 for SELECT option, specify the output
file name and library (must be created in QTEMP, for a
temporary file). Press ENTER twice.

Then, type the following query -
select * from SAHAA/EMPDATA1
where rrn(EMPDATA1) in(
SELECT distinct dec((rrn(EMPDATA1)/2)) FROM SAHAA/EMPDATA1
) --> use your file and library name

Press Enter, the message File TEMP in QTEMP was created.
Restore the previous session attributes as follows -

Press Shift + F1, option1 Change session attributes.Type 1
for SELECT option. Press ENTER thrice.

Now, we would have half no. of records of EMPDATA1 in file
TEMP (say)

Now, simply use the CPYF command to copy TEMP file data
into EMPDATA1 as follows -

CPYF FROMFILE(QTEMP/TEMP) TOFILE(SAHAA/EMPDATA1) MBROPT
(*REPLACE)

Note:- TEMP is the file temporarily created in QTEMP, so
that it is no longer present in the system when the session
expires.

Answer / kumar

ohh.. just manage the ws-count variable..

Answer / kumar

Hi ............
try this one ....might be a logical one ... but have to
give customers what they want.....

Select count(*) into :ws-count from emp with ur;

Declare CURSOR cursor1 for
Select *(if possible better code all fields)
from emp where count(*) < :ws-count;
Open cursor
Fetch cursor.

Declare CURSOR cursor2 for
Select *(if possible better code all fields)
from emp where

count(*) >= :ws-
count;
Open cursor
Fetch cursor.

Can we use group-by clause in sub-query? If 'yes' means,Will it be executed successfully or else If 'no' means why should we not using that method? Give me your suggestion please....

1 Answers  

What is role in db2?

0 Answers  

what is the steps followed in EXPLAIN Process or EXPLAIN command. (Explain is for identifying the optimized access path but how or what is the steps for doing the EXPLAIN)

4 Answers   IBM,

AGGREGATE function support by DB2. A) SUM & AVG B) SUM, MIN & AVG C) SUM, MAX, AVG , MIN &COUNT D) NONE

2 Answers   Accenture,

What does the CHECK Utility do ?

1 Answers  

If there is no row in Emp table with Ename as Raghava and you run the below queries in SPUFI. 1.select Ename from EMP wher Ename = 'raghava' 2.Select count(*) from EMP where Ename = 'raghava' What is SQLCODE shown up in SPUFI

4 Answers   ADP,

What statistic will tell the DBA how must space can be reclaimed from dropped table spaces on the next reorg run?

1 Answers  

What is dbrm library?

0 Answers  

What is a clustering index?

2 Answers  

Can we update view in db2?

0 Answers  

In a DB2-CICS program which is acts as co-ordinator and which is participant?

1 Answers   IBM,

what happend with out using Commit,when closing cursor and program?

5 Answers   CSC,