A few options. The SQL you supplied is as follows:
WHERE H.UpdDate > current_date - 2
Assumming H.UpDate is a DATE or TIMESTAMP field then you want to be using something like:
WHERE H.UpdDate > CURRENT DATE - 2 DAYS
Alternatively, if "current_da te" is an integer application value that you are subtracting 2 from as part of the comparison then the error indicates that H.Update...
Snib
Last Activity: Jul 19 '07, 03:09 PM
Joined: Mar 16 '07
Location:
-
OK, so does that mean you still have a problem or is everything no working ok?
If you still have a problem it would help if you could run an EXPLAIN for each piece of SQL against each environment and then post the details from the PLANTABLE. We can then review exactly what order the SQL is being executed in and the indexes DB2 has selected.
One other thing, before you run the EXPLAIN ensure you have executed RUNSTATS...Leave a comment:
-
As far as i can tell the problem seems to be that you have multiple A and D records will the same polnum value. So I do not mean just one A record and one D record for a single polnum value but, for examples, 2 A records and 1 D record with the same polnum value.
On the second query this would just show as 1 on the count, but on your first SQL this would show as 2 as the SQL in the first statements joins all A records to all D records...Leave a comment:
-
A "BIND REPLACE" will require a DBRM as input and will replace the existing data with that supplied in the DBRM. It will then analyse the access paths for the SQL supplied in the DBRM.
A "REBIND" re-calculates the access paths for the existing SQL data in DB2. It does not take a DBRM as input as it is using the existing DBRM data loaded by a prior BIND statement.
You tend to run a "BIND REPLACE"...Leave a comment:
-
Assuming that the SQL you meant to post was as follows:
SELECT D.STORE_FACILIT Y_ID,
D.PRIMARY_ACCT_ NUM
FROM DS_STORE D
WHERE D.PRIMARY_ACCT_ NUM IN
(SELECT W.PRIMARY_ACCT_NU M
FROM WK_FILTER_CMPNT W
WHERE W.COMPONENT1_ID = 1)
FOR FETCH ONLY
Then there are a few things to consider:
1/ Temporary...Leave a comment:
-
Ben is correct in what he has stated. If you check the the following URL:
Index Design for Performance
Under the section titled "Recommendation s" it confirms that DB2 will select the first index added to the table as the clustering index if that is the only index being added at that time.
What is not clear from your statements is if/when you ran a RE-ORG, when you loaded the tables and whether...Leave a comment:
-
I seem too remember (and I could be completely wrong!) that when you are not identifying the rows DB2 attempts to add the value in the first column on the input table to the first column on the output table. It does not do anything clever like checking the if the columns are named the same. So the error message indicates that the first column on the live table is not of a compatible type with the archive table - probably stating the obvious but then...Leave a comment:
-
Winnie,
You should be able to do this with the follow Db2 SQL:
SELECT ResellerKey, ''
FROM A
UNION
SELECT '' , CustomerKe
FROM B
If '' does not work then use ' ' - basically the same but with a space between the singel quotes. If ResellerKey or CustomerKe are numeric then just replace '' with 0.
Regards
Snib...Leave a comment:
-
If I understand that SQL correctly it is returning the date for the first day of the current month in US date format (MM/DD/YYYY).
So, something like this should do the trick:
select strip(char(mont h(current date))) concat '/1/' concat
char(year(curre nt date))
from sysibm.sysdummy 1
; ...Leave a comment:
-
I have just tried you SQL with the following:
select YEAR(CURRENT DATE - 1 DAY) * 10000 + MONTH(CURRENT DATE - 1 DAY)
* 100 + DAY(CURRENT DATE - 1 DAY)
from sysibm.sysdummy 1
;
and got the result:
20070410
which...Leave a comment:
-
I did a bit more digging, you can use IDENTITY and SEQUENCE columns. If you have Db2 V9 the SEQUENCE column would seem to be the best option but you do need to generate each value, but this can be done with a trigger fired by the INSERT.
Regards
SnibLeave a comment:
-
Another cause I have come across is when the NLEVELS (think this is the row name) on for the index exceeds 3 or 4. This values indicates the maximum (I think) number of index levels that DB2 has to navigate to get to a leaf page - which contains the the actual index data. If I remember correctly 3 is the most people expect to see on a well organised index, rarely 4. If the nlevels is more than this it can cause performance problems.
...Leave a comment:
-
I just found a reference for the DB2 SEQUENCE type, the next thing in UDB after "IDENTIY COLUMNS"! Have a look at this URL for further details:
http://www-128.ibm.com/developerworks/db2/library/techarticle/0205pilaka/0205pilaka2.htm l
Seems you have a few options if you have DB2 Version 9 - you learn something new every day!
Regards
SnibLeave a comment:
-
You may want to look into SYSPACKDEP and determine which packages are using any of the tables in the tablespaces you have re-org'd.
Are you running a rebind after the RE-ORG and RUNSTATS?
If not then DB2 may not be using the best path to access the data after the RE-ORG.
Also, did you check the runstats before with the runstasts after the "RE-ORG/RUNSTAT" activity to understand what changed?...Leave a comment:
-
I found something else that you may want to look into, Not sure exactly what version of DB2 this came in with, also not sure of performance overhead etc.
http://publib.boulder. ibm.com/infocenter/db2luw/v8/index.jsp?topic =/com.ibm.db2.udb .doc/admin/t0004990.htm
I would definitely advice using the "GENERATED ALWAYS" option to ensure that the applications cannot set the values.
Apparently once you...Leave a comment:
-
KarenC,
See my replies to:
http://www.thescripts. com/forum/thread615216.ht ml
and
http://www.thescripts. com/forum/thread615216.ht ml
I have always done this as a seperate unit of work in DB2 due to roll-back and duplications issues. If I was using MS Access I would use the "AutoNumber " field type but I have not yet figured out a way to replicate this in DB2....Leave a comment:
-
Manoj,
The problem I can see with attempting to get DB2 to auto-generate a sequence number using a stored procedure or trigger is the issue of rollback in case of failure. Generally you set up another table to hold tha last sequence number issued . At the start of the unit of work you get the next sequence number from the sequence store table, increment the value and store this new value on the sequence store table. You then commit...Leave a comment:
-
TC2,
I set the table up in MS Access to try out the SQL so you will need to convert the nested IIF statments into CASE statement but beyond that the SQL is as per the column names you supplied.
The SQL will not cater for the possibility off there being more than 2 addresses for one contact with the same catergory as there is nothing on your tables to distinguish between the rows. It would return multiple rows in this...Leave a comment:
-
Depends whether you mean you want to remove the column from the table altogether or just remove the date in the column,
If the column is CHAR then do something like this
UPDATE TABLE_NAME
SET CHAR_COLUMN = ""
This will set the column on all rows to an empty string.
If yuo want to remove a column from the table then that is a little more difficult. Depends on what utilities...Leave a comment:
-
Sorry, I read ROW instead of column so please ignore my previous response as it is definitely wrong!
If you are altering a table structure in a COBOL program then you will be affecting the SYSCOLUMNS catalogue table. You will potentially, via relationships in SYSPACKDEP, also invalidate 1 or more packages that use the table. You will also affect changes to SYSTABLES ( I think - can't find my reference guide to check the columns on...Leave a comment:
No activity results to display
Leave a comment: