I have found the cause of the problem.
When changing the schema name in the DB2LOOK output, the new name was entered in lower case.
Repeating the exercise with upper case, everything worked OK.
Brian
This should be a trivial question, but it is causing problems as our DBA has left. The DBA was also the UNIX administrator.
User FRED has a v8 database on AIX to use for testing.
We have a new developer BERT, and we wish to give him a copy of the database.
Bert has DBA authority, so he runs...
I was in my mid 50s, and had been successfully contracting as a Bull mainframe Cobol analyst / programmer in the UK. This was a dying arena and work was becoming scarce.
A customer for whom I had been contracted on and off for 20 years decided that they were going to redevelop their main...
Eliezer,
If you still want it to be left justified (ie you are trying to get rid of some of the leading zeros, you could use:
select substr(digits(count(*)),6,5) from crdcrm.employee where firstname = 'John'
Brian
Ties,
I did retire properly at age 62 after 40 years in the industry. A couple of months ago, my last employer phoned to say that a couple of key staff had left, and asked if I could work for a few months while replacements were brought up to speed.
I will be around until the end of April...
Thanks for your input on this.
My problem is purely on performance, as I have to extract details of the latest cash payment for over a million customers, where each customer has been paying fortnightly for several years.
My correlated sub-query will process the payments twice for each...
I frequently have to write queries such as:
select
a.custno,a.payref,a.payamt
from
tab a
where
a.payref =
(select
max(b.payref)
from
tab b
where
b.custno = a.custno and
b.paytype = 1)
This query should give details of...
This is another variant to be considered
select t1.a
, t1.b
, t1.c
,case
when t1.b = 2 then
(select coalesce(t2.k,' ') from tab02 t2
where t2.c = t1.c)
else ' '
end
from tab01 t1
Regards
Brian
I noticed that in the first example you used DAY(...). This extracts the day portion of the date.
DAYS(..) extracts the relative day number.
TIMESTAMP is used for datetime fields in the format yyyy-mm-dd-hh.mm.ss.nnnnnn
Like Ties, i cannoet quite see you requirement.
Perhaps you could try left outer joins to alias tables.
This may be inefficient, so it depends on volumes.
Brian
(PS this is my last post to TT as I retire today)
Your only sure solution is to extract information from SYSCAT.COLUMNS and to use that information to parse the field.
If you have no access to the system tables, get your DBA to produce an extract for you to use.
Brian
It is possible to update using Freehand SQL. In supervisor the restriction on SQL to be 'Select only' must be removed for the user.
I agree with Ties that you must be very careful in allowing this facility to end users.
Brian
db2 "values substr(left(monthname(d1),3)||'-'||char(year(d1)),1,8)"
The complications are there because db2 keeps trying to return a varchar field. There may be a simpler way of doing this, but it works.
Brian
Using aliases in raw SQL against a DB2 database, I managed to get a result. OK this is a simple query, but you may be able to make the left outer joins do what you want.
select
cu_cust_no
,case when b.ca_ref_no is null then a.ca_addr1 else b.ca_pcode end
from
customer left outer...
From the IBM help files:
SMALLINT
(500 or 501)
01 name PIC S9(4) COMP-5. 16-bit signed integer
INTEGER
(496 or 497)
01 name PIC S9(9) COMP-5. 32-bit signed integer
BIGINT
(492 or 493)
01 name PIC S9(18) COMP-5. 64-bit signed integer
DECIMAL(p,s)
(484 or 485)
01 name PIC...
Is it possible to allow complex freehand SQL when querying against DB2.
I am trying to use the 'WITH' structure to create a temporary table repeatedly used in a long query containg about 10 UNIONS.
The error message says that I am only allowed to use SELECT statements.
Thanks
Brian
Basically, this is not normally a BO problem, but a database problem.
Try with just one reference number. This could give an indication as to whether a table scan is being used.
How are you getting the list of reference numbers in? Are the appropriate indexes set up on the database...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.