MVSFORUMS.com

Database :: RE: 0 = 1

Author: papadi
Posted: Tue Mar 09, 2010 4:58 pm (GMT -5)
Topic Replies: 9

Quote:

is it considered a good way to solve a db2 performance problem?
how can an avarege guy like me know when to use this trick?

Typically, tricks should not be used. As with your situation, they often cause confusion. If some "trick" is to be used, it should be well documented and approved by the dba(s).

Also, an upgrade could improve the situation being circumvented by some "trick" and the improvement would not be seen because of the "trick".

Quote:

could you provide some guidance?

Analyze what a query is going to generate "under the covers". Work with the database support people. Use EXPLAIN. Run and measure real-world tests.
_________________
All the best,

di

Database :: RE: 0 = 1

Author: coboladdict
Posted: Tue Mar 09, 2010 7:31 am (GMT -5)
Topic Replies: 9

is it considered a good way to solve a db2 performance problem?
how can an avarege guy like me know when to use this trick?
could you provide some guidance?
thanks.

Database :: RE: 0 = 1

Author: CZerfas
Posted: Tue Mar 09, 2010 5:02 am (GMT -5)
Topic Replies: 9

By combining the predicate '0 = 1' with an OR to another predicate, you force DB2 not to consider index access for this column.

Probably irritated with missing runstats values for this column the DB2 optimizer could use the index on DD_BAL, which actually could lead to longer access paths.

regards
Christian

Application Programming :: RE: put GDG number in output file.

Author: DaveyC
Posted: Tue Mar 09, 2010 1:45 am (GMT -5)
Topic Replies: 10

Here is the code for a locate routine. Linkage is left as an exercise.

Code:

LOCATE CSECT , 00290105
LOCATE AMODE 31 00290205
LOCATE RMODE ANY 00290305
SAVE (14,12) .Save callers registers 00290605
LR R12,R15 .R12=base 00290705
USING LOCATE,R12 .Addressability 00290805
ICM R11,15,0(R1) .-> Parameter List Structure 00380000
USING LOCPLIST,R11 .Parameter List Structure addressing 00390000
*---------------------------------------------------------------------* 00400004
* Locate the DSN * 00410004
*---------------------------------------------------------------------* 00420004
MVC LOCCAML(CAMLSTL),CAMLST .Init CAMLST 00430004
LA R1,LOCDSN .DSN 00440004
ST R1,LOCCAML+4 .Put into CAMLST 00450004
LA R1,LOCAREA .Work Area 00460004
ST R1,LOCCAML+12 .Put into CAMLST 00470004
LOCATE LOCCAML .Look in Catalog for DSN 00480004
LTR R15,R15 .Cataloged? 00490004
BNZ EXIT .N: 00491004
MVC LOCWAREA(6),LOCAREA+6 .Set VOLSER from catalog 00492004
MVI LOCWAREA+6,C' ' .7 byte VOLSER for MIGRAT 00493004
CLC LOCWAREA(6),=CL8'MIGRAT' .Dataset is HSM migrated? 00494004
BNE EXIT .N: Finished 00495004
TM LOCAREA+4,X'20' .DASD? 00496004
BO HSMMIGL1 .Y: 00497004
TM LOCAREA+4,X'80' .Tape? 00498004
BO HSMMIGL2 .Y: 00499004
B EXIT .Return to caller 00499104
HSMMIGL1 MVI LOCWAREA+6,C'1' .Migration Level 1 - DASD 00499204
B EXIT .Return to caller 00499304
HSMMIGL2 MVI LOCWAREA+6,C'2' .Migration Level 2 - Tape 00499404
B EXIT .Pass back return code 00499504
*---------------------------------------------------------------------* 00500000
* Exit * 00510000
*---------------------------------------------------------------------* 00520000
EXITNEG L R15,=F'-1' .RC=Negative, PLIST is in error 00530000
B EXIT 00540000
EXITPOS LA R15,8 .RC=8, LOCATE request failed 00550000
B EXIT 00560000
EXIT0 SR R15,R15 .RC=0, LOCATE request successful 00570000
EXIT RETURN (14,12),RC=(15) .Return to caller 00580000
EJECT , 00590000
*---------------------------------------------------------------------* 00600000
* Constants * 00610000
*---------------------------------------------------------------------* 00620000
CAMLST CAMLST NAME,0,,0 .LOCATE SVC Parameter List 00630000
CAMLSTL EQU *-CAMLST 00640000
LTORG , .Literal Pool Origin 00650000
EJECT , 00660000
*---------------------------------------------------------------------* 00670000
* Locate DSN Parameter List * 00680000
*---------------------------------------------------------------------* 00690000
LOCPLIST DSECT , .Locate Parameter List 00700000
LOCDSN DS CL44 .Data Set Name 00710000
LOCWAREA DS 0D,CL300 .Work area 00720000
ORG LOCWAREA .Map our work area 00730000
LOCCAML CAMLST NAME,0,,0 .LOCATE SVC Parameter List 00740000
LOCAREA DS 268C .LOCATE SVC Work Area 00750000
ORG , 00760000
LOC1LEN EQU *-LOCPLIST .Version 1 Plist length 00770000
EJECT , 00780000
*---------------------------------------------------------------------* 00790000
* System macros * 00800000
*---------------------------------------------------------------------* 00810000
YREGS , .Register equates 00820000
END 00830000

_________________
Dave Crayford

Database :: RE: 0 = 1

Author: coboladdict
Posted: Mon Mar 08, 2010 6:02 pm (GMT -5)
Topic Replies: 9

Actually, it's not AND but OR.
Still doesn't make sense to me.
Thanks.

Database :: RE: 0 = 1

Author: coboladdict
Posted: Mon Mar 08, 2010 5:44 pm (GMT -5)
Topic Replies: 9

Ok,
Here goes the entire query:

Code:

SELECT ...
FROM TABLE
WHERE CD_PRF_DEPE = :WCD-PRF-DEPE
AND CD_INST = :WCD-INST
AND (DD_BAL = :WDD-BAL-INI OR 0 = 1)
AND NR_PT_CTB = :WNR-PT-CTB-INI
AND DT_BAL = :WDT-BAL-INI
AND NR_SEQL_LCTO = :WNR-SEQL-LCTO-INI
AND NR_DOC = :WNR-DOC-INI
ORDER BY CD_PRF_DEPE, DD_BAL, CD_TIP_OBJ, CD_OBJ, ...

Thanks.

Database :: RE: 0 = 1

Author: papadi
Posted: Mon Mar 08, 2010 4:47 pm (GMT -5)
Topic Replies: 9

Most likely, the goal is to return no rows. . . The first query would have to process rows which would take resources. The second query need not as the literals will never be equal.

Is this part of some larger query? If so, post the entire query. . .
_________________
All the best,

di

Database :: RE: 0 = 1

Author: coboladdict
Posted: Mon Mar 08, 2010 11:46 am (GMT -5)
Topic Replies: 9

Quote:

It runs faster because it is automatically NOT= without even looking at the database columns.

if this condition is never satisfied, how can the query retrieve any line from the table?
thanks.

Database :: RE: 0 = 1

Author: Anuj Dhawan
Posted: Mon Mar 08, 2010 6:44 am (GMT -5)
Topic Replies: 9

I'm not very sure how the performance is better for 2nd query - it's(0=1) used in an "AND" operation... . Do you have explain statistics with you?
_________________
Regards,
Anuj

Application Programming :: RE: CEEGTST and vars initialization

Author: papadi
Posted: Sun Mar 07, 2010 10:06 pm (GMT -5)
Topic Replies: 1

Not that i'm aware of. Go for the MOVEs after getting the memory.
_________________
All the best,

di

Database :: RE: 0 = 1

Author: papadi
Posted: Sun Mar 07, 2010 10:05 pm (GMT -5)
Topic Replies: 9

It is as you say - a condition that can never be satisfied. That is how it works. . .

It runs faster because it is automatically NOT= without even looking at the database columns.

Also, i suspect that for whatever reason, you did not post the complete sql but rather only part of it. . .
_________________
All the best,

di

Application Programming :: CEEGTST and vars initialization

Author: coboladdict
Subject: CEEGTST and vars initialization
Posted: Sun Mar 07, 2010 8:16 am (GMT -5)
Topic Replies: 1

Is there a way to have the memory got with CEEGTST or CEECZST initialized?
I have an array in this piece of memory and need to initialize its variables on my own (initialize �, move 0 to �, move space to �) after getting the memory.
Can LE return me this piece of memory already initialized according to my need?

Database :: 0 = 1

Author: coboladdict
Subject: 0 = 1
Posted: Sun Mar 07, 2010 7:57 am (GMT -5)
Topic Replies: 9

Code:

select col1, col2, col3
from table1
where cola = 123
and colb = 456

we had a query with the code above.
the performance wasn't good enough and the db2 guy in the company suggested the following:

Code:

select col1, col2, col3
from table1
where cola = 123
and colb = 456
AND 0 = 1

the performance is way better now.
i don't understand the purpose of this "0=1".
to me, it's a condition that can never be satisfied.
can someone explain how it works, please?
thanks.

Utilities :: RE: sort sum - expand output length

Author: Frank Yaeger
Posted: Fri Mar 05, 2010 2:02 pm (GMT -5)
Topic Replies: 1

This would exapnd the CUST-QTY to PIC S9(12)V999:

Code:

INREC BUILD=(1,9,10:Z,40,7)
SORT FIELDS=(1,9,CH,A)
SUM FIELDS=(10,8,PD)

Z is used to insert X'00' before the PD value to expand it.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort/

Utilities :: sort sum - expand output length

Author: sub
Subject: sort sum - expand output length
Posted: Fri Mar 05, 2010 1:24 pm (GMT -5)
Topic Replies: 1

Hi,
I have a input file which has 2 fields. The layout is
01 INPUT-REC
05 CUST-NO PIC X(9)
05 CUST-DESC PIC X(30)
05 CUST-QTY PIC S9(10)V999 COMP-3

The input file has many records for a single cust-no. I am trying to have only 1 record per cust-no and sum cust-qty to an output file.

I used the below given sortcard
SORT FIELDS=(1,9,CH,A)
SUM FIELDS=(40,7,PD)
OUTREC FIELDS=(1:1,9,10:40,7)

However, if the CUST-QTY for 2 records is already at the max for a single CUST-NO then the sort sum doesn't work and I get multiple records.
For eg., it doesn't work for the below condition.
Cust-NO CUST-QTY
XXXXXXX1X -4004000000.000
XXXXXXX1X -8000000000.000

I want to expand the CUST-QTY for the output record to avoid this issue. Can someone please advise how to resolve this using SORT.

Thanks,
Sub