|
|
|
|
|
|
| Author |
Message |
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
 Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Concatenate column values from multiple rows
|
|
|
TD wrote:
| Quote: |
I borrowed this very useful piece of code and modified it for my table
definitions. It works very well with the exception of the warning of a
possible infinite loop in the Y table as defined. Is there any way this
can be removed ? The max common values for each record I have in my table
2 [or second table] is say 10, that being there are a max number of 10
records to concat but they vary from 1-10. I am also concating with a '+'
between each value but is always adds a '+' to the final value as well..
Further I am restricting the X table to a single FKEY value and using it
as a UDF I can call from a query or SQL SP.
|
I already answered that ony off-line 'cause I didn't knew that you
cross-posted this to the NG as well.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena |
|
| Back to top |
|
 |
Thiru
*nix forums addict
Joined: 20 May 2005
Posts: 80
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Make easy money!! NO SCAM!! Please Read!
|
|
|
This is not a right place...
Thiru |
|
| Back to top |
|
|
Giganews
*nix forums beginner
Joined: 26 May 2005
Posts: 4
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Create concatenated string from a result set using SQL
|
|
|
People, I know I am reposting here, but I'm really stuck on this one. I have
a similar issue as the below. I have borrowed Knut's code which works well,
but always returns the warning of a possible infinite loop. I read up as mch
as I can on recursive SQL and I cannot for the life of me find a way to
return the result without getting the warning.
I have tried limiting the result the a single value in the below PAX table,
and tried to use a max value in the PAX Item table but I still get a good
result AND the warning.
If some kind soul could PLEASE demonstrate how this could be done without an
infinite loop warning I would be most grateful.
many thanks,
Tim
"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:chjv91$rkg$1@fsuj29.rz.uni-jena.de...
| Quote: | Rudolf Bargholz wrote:
Hi ,
I have the following tables
-------------
PAX:
Id
Order_Id
Name
Position
Id OrderId Name Position
1 1 PersonA 1
5 1 PersonB 2
19 1 PersonC 3
25 1 PersonD 4
-------------
ITEM
Id
Order_Id
Name
Id OrderId Name
1 1 Item1
2 1 Item2
-------------
PAX_ITEM
Pax_Id
Item_Id
Pax_Id Item_Id
1 1
5 1
25 1
5 2
19 2
-------------
One Item can be connected to numerous Passengers and a Passenger can be
connected to numerous Items. What I need is a list of items and the
associated Pax (Passengers) in the format:
ITEM
Id Order_Id PaxString
1 1 " 1 2 4"
2 1 " 2 3"
What you want to do is essentially an aggregation. There are some
articles
that describe how you could do it with external routines, i.e. UDFs:
http://www-106.ibm.com/developerworks/db2/library/techarticle/0309stolze/0309stolze.html
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0404stolze/index.html
Now, let's get back to your problem and solve it with pure SQL. If I got
this right, then you want to know for each item the Id of the order and
also the "Position"s of the passengers, concatenated to the string. I
really don't quite understand the relations between Orders, Items and
Passengers. For example:
(1) can an item appear in multiple orders and how do you want to see that
in
the output
(2) you store the item-order information redundant in the PAX and in the
ITEM table - why?
(3) I would have thought that a passenger has orders and each order has
items with positions. What's your scenario here because it is obviously
different?
Well, despite those unclear things, I'll give it a try...
WITH
-- a numbered list of all "positions" for an item-id
pax_list(item_id, order_id, row_num, pax_position) AS (
SELECT i.item_id, p.order_id,
row_number() over(PARTITION BY i.item_id ORDER BY
p.position),
p.position
FROM pax_item AS i JOIN pax AS p ON
i.pax_id = p.id ),
-- recursive portion of the query to build the lists
all_lists(item_id, order_id, list, count) AS (
SELECT item_id, order_id,
VARCHAR(RTRIM(CHAR(pax_position)), 3500), 1
FROM pax_list
WHERE row_num = 1
UNION ALL
SELECT p.item_id, p.order_id,
a.list CONCAT ' ' CONCAT RTRIM(CHAR(p.pax_position)),
a.count + 1
FROM pax_list AS p, all_lists AS a
WHERE p.item_id = a.item_id AND
p.order_id = a.order_id AND
p.row_num = a.count + 1 ),
-- find the last list for each item
last_list(item_id, order_id, final_list) AS (
SELECT l.item_id, l.order_id, l.list
FROM all_lists AS l
WHERE l.count >= ALL ( SELECT m.count
FROM all_lists AS m
WHERE l.item_id = m.item_id AND
l.order_id = m.order_id ) )
SELECT item_id, order_id, final_list AS paxstring
FROM last_list
@
ITEM_ID ORDER_ID PAXSTRING
----------- ----------- ----------------------------------------
SQL0347W The recursive common table expression "STOLZE.ALL_LISTS" may
contain
an infinite loop. SQLSTATE=01605
2 1 2 3
1 1 1 2 4
2 record(s) selected with 1 warning messages printed.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena |
|
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Create concatenated string from a result set using SQL
|
|
|
DB2 recognizes simple patterns in predicates which ensure finite
recursion. The safest is a strictly monotonic integer value with a limit:
WITH threeaplusone(lvl, a)
AS (VALUES (1, CAST(12345 AS BIGINT))
UNION ALL
SELECT lvl + 1, CASE WHEN (a / 2) * 2 = a
THEN a / 2
ELSE 3 * a + 1 END
FROM threeaplusone
WHERE lvl < 10000 AND a <> 1)
SELECT max(lvl) FROM threeaplusone;
DB2 knows that when you add 1 to a numeric type it will eventually
exceed any number....
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Rhino
*nix forums Guru
Joined: 08 Feb 2005
Posts: 449
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Create concatenated string from a result set using SQL
|
|
|
According to some old course notes I found, "any recursive SQL statement
that does not use a control variable will receive an SQL warning
(SQL0347W)". Therefore, if you absolutely cannot tolerate getting that
warning message and simply ignoring it, you need to add a control variable.
The example from the course is rather involved and is part of a multi-page
topic. I'm really not keen on typing that much  However, this is the
information from the main page that talks about this issue and I think it
probably illustrates the technique well enough without forcing you to wade
through several pages of surrounding material - and it avoids me having to
type it all!
Here is the page in question; I've capitalized the bits of the example that
are emphasized in the original graphic to get the same approximate effect;
the '0' in the initialization select should also be emphasized but I can't
think of a way to do that without confusing the syntax.
================================================================
Controlling Depth of Recursion
[Example graphic:]
with rpl (LEVEL, part, subpart, quantity) as
(
select 0, root.part, root.subpart, root.quantity
from partlist root
where root.part = '00'
union all
select PARENT.LEVEL+1, child.part, child.subpart, child.quantity
from rpl parent, partlist child
where parent.subpart = child.part
and PARENT.LEVEL < 2
)
select LEVEL, part, subpart, sum(quantity) as quantity
from rpl
group by level, part, subpart
[The first Select in the example is labelled 'Initialization Select', the
second select is labelled 'Iterative Select' and the third select is
labelled 'Main Select'.]
Notes:
Recursion normally continues until all parts have been resolved into their
components. However, the depth of the recursion can be controlled by
simulating the addition of a control column to the table.
This control column is initialized arbitrarily to 0 in the initialization
select and increased by one on every execution of the iterative select. A
condition in the WHERE clause of the iterative select is used to ensure that
the iteration only continues for a fixed number of levels.
In the example, the control column is named LEVEL. It is set to an integer
value of 0 in the initialization select. The WHERE clause of the
initialization select determines the value in the PART column with which the
table expression begins. This time, we are interested in the breakdown of
Part 00, but we could have started with any part number we were interested
in. The initial value of LEVEL would still be 0, regardless of the starting
part number.
The iterative select increments the LEVEL value by adding 1 on each
iteration. The condition
PARENT.LEVEL < 2
in the WHERE clause of the iterative select is used to limit the number of
iterations: simply set the constant to the number of iterations which are
desired.
The main select displays the results of the table expression. The LEVEL
column in the final result makes the origin of each result row clear: rows
that came from the initialization select have a level of 0, rows from the
first iteration have a level of 1, rows from the second iteration have a
level of 2, and so on. The ORDER BY puts the result in a convenient
sequence.
NOTE: LEVEL is not a column of table PARTLIST. It does not have to be added
to the table PARTLIST via an ALTER TABLE statement. It is a "virtual" column
created by the SQL statement.
================================================================
I hope this answers your question satisfactorily.
Rhino
"Giganews" <tdavidge@no_spam.hotmail.com> wrote in message
news:p-ydnTH01c_dTbjfRVn-iQ@comcast.com...
| Quote: | People, I know I am reposting here, but I'm really stuck on this one. I
have
a similar issue as the below. I have borrowed Knut's code which works
well,
but always returns the warning of a possible infinite loop. I read up as
mch
as I can on recursive SQL and I cannot for the life of me find a way to
return the result without getting the warning.
I have tried limiting the result the a single value in the below PAX
table,
and tried to use a max value in the PAX Item table but I still get a good
result AND the warning.
If some kind soul could PLEASE demonstrate how this could be done without
an
infinite loop warning I would be most grateful.
many thanks,
Tim
"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:chjv91$rkg$1@fsuj29.rz.uni-jena.de...
Rudolf Bargholz wrote:
Hi ,
I have the following tables
-------------
PAX:
Id
Order_Id
Name
Position
Id OrderId Name Position
1 1 PersonA 1
5 1 PersonB 2
19 1 PersonC 3
25 1 PersonD 4
-------------
ITEM
Id
Order_Id
Name
Id OrderId Name
1 1 Item1
2 1 Item2
-------------
PAX_ITEM
Pax_Id
Item_Id
Pax_Id Item_Id
1 1
5 1
25 1
5 2
19 2
-------------
One Item can be connected to numerous Passengers and a Passenger can
be
connected to numerous Items. What I need is a list of items and the
associated Pax (Passengers) in the format:
ITEM
Id Order_Id PaxString
1 1 " 1 2 4"
2 1 " 2 3"
What you want to do is essentially an aggregation. There are some
articles
that describe how you could do it with external routines, i.e. UDFs:
http://www-106.ibm.com/developerworks/db2/library/techarticle/0309stolze/0309stolze.html
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0404stolze/index.html
Now, let's get back to your problem and solve it with pure SQL. If I
got
this right, then you want to know for each item the Id of the order and
also the "Position"s of the passengers, concatenated to the string. I
really don't quite understand the relations between Orders, Items and
Passengers. For example:
(1) can an item appear in multiple orders and how do you want to see
that
in
the output
(2) you store the item-order information redundant in the PAX and in the
ITEM table - why?
(3) I would have thought that a passenger has orders and each order has
items with positions. What's your scenario here because it is obviously
different?
Well, despite those unclear things, I'll give it a try...
WITH
-- a numbered list of all "positions" for an item-id
pax_list(item_id, order_id, row_num, pax_position) AS (
SELECT i.item_id, p.order_id,
row_number() over(PARTITION BY i.item_id ORDER BY
p.position),
p.position
FROM pax_item AS i JOIN pax AS p ON
i.pax_id = p.id ),
-- recursive portion of the query to build the lists
all_lists(item_id, order_id, list, count) AS (
SELECT item_id, order_id,
VARCHAR(RTRIM(CHAR(pax_position)), 3500), 1
FROM pax_list
WHERE row_num = 1
UNION ALL
SELECT p.item_id, p.order_id,
a.list CONCAT ' ' CONCAT RTRIM(CHAR(p.pax_position)),
a.count + 1
FROM pax_list AS p, all_lists AS a
WHERE p.item_id = a.item_id AND
p.order_id = a.order_id AND
p.row_num = a.count + 1 ),
-- find the last list for each item
last_list(item_id, order_id, final_list) AS (
SELECT l.item_id, l.order_id, l.list
FROM all_lists AS l
WHERE l.count >= ALL ( SELECT m.count
FROM all_lists AS m
WHERE l.item_id = m.item_id AND
l.order_id = m.order_id ) )
SELECT item_id, order_id, final_list AS paxstring
FROM last_list
@
ITEM_ID ORDER_ID PAXSTRING
----------- ----------- ----------------------------------------
SQL0347W The recursive common table expression "STOLZE.ALL_LISTS" may
contain
an infinite loop. SQLSTATE=01605
2 1 2 3
1 1 1 2 4
2 record(s) selected with 1 warning messages printed.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
|
|
|
| Back to top |
|
|
gnuoytr@rcn.com
*nix forums beginner
Joined: 26 May 2005
Posts: 7
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Tips and tricks for fast SQL procedures
|
|
|
Serge Rielau wrote:
| Quote: | ak_tiredofspam@yahoo.com wrote:
very interesting, thank you Serge.
Just wanted to add one comment on "The cache also remembers
previously
executed statements, so that after the first compilation of the SQL
statement. Subsequent invocations will simply keep executing the
same
execution plan."
I strongly believe that executing one and the same execution plan
is
not always efficient. For data skew situations, range queries and
very
complex queries, we sometimes are better off with dynamic SQL (but
only
for large tables). What do you think?
Absolutely. For that purpose DB2 V8.2 supports REOPT(ALWAYS). It
will,
recompile a statement every time.
The next "crank at the handle" so to speak is REOPT(AUTOMATIC). In
this
case the optimizer knows for which value ranges the plan is good and
will kick of recompilation if the safe-zone is left. This capability
is
not yet in the product 
REOPT(ONCE), which is supported, waits for the first set of values,
assuming that it is representative. E.g. when you have a query to
find
executives above a certain salary REOPT(ONCE) is quite sufficient
for
the optimizer to figure out that salary is going to be highly
filtering.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
|
as usual, the download from *ibm*.com won't finish (why is that,
anyway??)
question:
i've read through most of the Yip, et al book, but i don't find any
specification of the scope of the dynamic SQL Cache: user, connection,
schema, instance??
thanks,
robert |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Tips and tricks for fast SQL procedures
|
|
|
gnuoytr@rcn.com wrote:
| Quote: | as usual, the download from *ibm*.com won't finish (why is that,
anyway??)
I've never had that problem. I used to have trouble with download |
accelerators downloading from the IBM site (corrupt files on unzip).
| Quote: | i've read through most of the Yip, et al book, but i don't find any
specification of the scope of the dynamic SQL Cache: user, connection,
schema, instance??
The cache is DB wide. Statements are matched based on their text, |
usage of certain special registers (like PATH, CURRENT SCHEMA) etc.
So it is common for many connections to share the same cached statement.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
gnuoytr@rcn.com
*nix forums beginner
Joined: 26 May 2005
Posts: 7
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Tips and tricks for fast SQL procedures
|
|
|
i'm home now. tried to read your article. came up immediately. must
be something with the CubeLand net.
found the 2nd edition at Borders. grabbed it: covers os/360 <G>
version. that's a big help.
some guy named Serge is prominent in the preface as being helpful in
bringing the book out.
back to the question: by DB wide, on mainframe, that means instance i
suppose.
thanks,
robert |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Tips and tricks for fast SQL procedures
|
|
|
gnuoytr@rcn.com wrote:
| Quote: | i'm home now. tried to read your article. came up immediately. must
be something with the CubeLand net.
found the 2nd edition at Borders. grabbed it: covers os/360 <G
version. that's a big help.
some guy named Serge is prominent in the preface as being helpful in
bringing the book out.
Another way of saying I'm loud ;-)
back to the question: by DB wide, on mainframe, that means instance i
suppose.
Uh, oh... I dare not comment on internal optimizations in DB2 zOS land. |
It is reasonable to expect that Db2 for zOS behaves the same.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: application program - question
|
|
|
Vavel wrote:
| Quote: | Hi all!
I want to insert the record into the table by
using an application program that includes the following statements:
EXEC SQL BEGIN DECLARE SECTION;
long hvInt_Stor;
long hvExt_Stor;
EXEC SQL END DECLARE SECTION;
hvInt_Stor = MMDB_STORAGE_TYPE_INTERNAL;
hvExt_Stor = MMDB_STORAGE_TYPE_EXTERNAL;
EXEC SQL INSERT INTO EMPLOYEE VALUES(
'128557', /*id*/
'Anita Jones', /*name*/
DB2IMAGE( /*Image Extender UDF*/
CURRENT SERVER, /*database server name in*/
/CURRENT SERVER register*/
'/employee/images/ajones.bmp' /*image source file*/
'ASIS', /*keep the image format*/
:hvInt_Stor, /*store image in DB as BLOB*/
'Anita''s picture'), /*comment*/
DB2AUDIO( /*Audio Extender UDF*/
CURRENT SERVER, /*database server name in*/
/*CURRENT SERVER register*/
'/employee/sounds/ajones.wav', /*audio source file*/
'WAVE', /* audio format */
:hvExt_Stor, /*retain content in server file*/
'Anita''s voice') /*comment*/
);
Maybe that's silly question,but : what compiler should I use? and which
libraries should I include? I toil over this for 2 weeks... I'm using
Microsoft Visual C++ at this moment( Borland C 5.5 compiler before),but
I can't compile this programme without errors (commonly errors are
inside library: sqltypes.h)
1. How to force this compiler to use IBM libaries only when it is
possible? 2. Maybe someone could tell me how to write programme,that
include statements that were shown above??
|
You are probably aware of this, but everything following the "EXEC SQL"
keywords is not valid C or C++ code. So the compiler should actually raise
some syntax errors. You need to precompile the code to translate the "EXEC
SQL" things into real C function calls. Have a look at the db2 precompiler
(http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/core/r0001964.htm)
for that.
Once you compiled the code you can link it into a library or executable. In
this step, you need to link in the library libdb2.a (exact name and
extension depends on you platform). Additionally, you might need libdb2api
and/or libdb2apie. I don't recall if you need an additional library for
the AIV Extender, but I don't think so.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena |
|
| Back to top |
|
|
Darin McBride
*nix forums Guru Wannabe
Joined: 28 Jul 2005
Posts: 100
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: 64-bit instance on Aix 32 bit kernel / 64 bit hardware
|
|
|
Jean-Marc Blaise wrote:
| Quote: | "Darin McBride" <dmcbride@naboo.to.org.no.spam.for.me> a écrit dans le
message de news:T0ZLd.250407$Xk.6730@pd7tw3no...
Larry E wrote:
Jean-Marc,
I believe what the technote is trying to say is you can install the
64-bit version of DB2 (i.e. the DB2 binaries) under a 32-bit OS, but
once you install the DB2 binaries, you must create DB2 instances in
order to operate a DB2 database. I believe it is saying that you cannot
create 64-bit instances under a 32-bit OS. If you didn't receive an
error in attempting to do so, I guess you are in uncharted territory,
and risk not being able to get support from IBM. I'm not sure that
there's any advantage to using a 64-bit instance under a 32-bit OS,
even if you are able to get it to work and it even if it IS supported.
My understanding is that AIX is a special case in that it works.
However, I don't think it's officially supported. I'm not sure if this
is explicitly unsupported, or simply implicitly unsupported by the fact
that the combination is not documented to be supported.
Hi Darin,
Thanks for your reply. This is not really clear to me; why does the
db2setup propose to create a 64-bit instance ? I think a 'bootinfo -K' can
be done to check if we are 32 or 64 bit kernel, and in case of 32-bit, it
should NOT propose to create a 64-bit instance, don't you think so ?
I agree that it seems crazy to do 64-bit "simulation" on a 32-bit kernel,
but there must be a reason somewhere as AIX does support it.
|
I doubt it's "simulation" - I expect that since the processor can
handle 64-bit, the 32-bit kernel was just modified to allow 64-bit
programs to be controlled by it.
And why would db2setup allow 64-bit? Simple - users run all sorts of
unsupported scenarios. Many of them work. DB2 doesn't exactly want to
say it's smarter than the user when it can help it - if you really need
to run 64-bit on a 32-bit kernel, db2setup should not prevent it. This
is different from DB2's autonomic computing in that you can turn that
off. Since db2setup is run before you can configure DB2, it's hard to
turn off autonomic computing functionality there. It's a delicate line
which I'm sure db2setup jumps from side to side depending on the issue,
but we're definitely open to feedback about which side of the line
db2setup should be on for each issue.
Here's a scenario that would be, in my mind, a valid reason to run
64-bit DB2 on 32-bit AIX kernel (which is not to say IBM supports it -
my opinion should not be construed as IBM policy or even IBM opinion).
There are still some limitations of the 32- and 64-bit kernel, as I
understand it. One would be that kernel extentions (such as file
system drivers) can only run in a certain kernel - the one that matches
how it was compiled. (e.g., DB2 Datalinks Manager has a file system
driver which only runs 32-bit.) So, if you have such a driver from
another vendor, which prevents you from using the 64-bit kernel, but
you need access to all 8GB of RAM for DB2, you really have little
choice but to go into this "unsupported" realm.
Should you then have an actual problem to report to IBM, you may need
to reboot to 64-bit kernel (disabling the other software) to reproduce
and report it, so that your problem comes up in a "supported"
environment. But for the rest of the time, you run in the 32-bit
kernel. |
|
| Back to top |
|
|
Who.Really.Really.Cares@g
*nix forums beginner
Joined: 26 May 2005
Posts: 2
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: How to reset/restart/modify the IDENTITY counter?
|
|
|
Serge Rielau wrote:
| Quote: | *sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time
you
catch up with 3 years worth of fixpack....
|
I'm running DB2 V7.1 for z/OS. (in original post I wrote 7.2 but it
proved to be a mistake).
| Quote: | PS: Makes me think just how much money I could make as a consultant
instead of a salary-man
I'm not sure what you're trying to say. Your smart procedure uses |
"ALTER TABLE ... ALTER COLUMN ... RESTART WITH ..." syntax, which is
not available to me. So your reply doesn't fit to my problem, I guess.
What I was trying to achieve was to simulate "RESTART WITH"
functionality.
WhoReallyCares |
|
| Back to top |
|
|
Thiru
*nix forums addict
Joined: 20 May 2005
Posts: 80
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Access DB2 UDB thru teriminals
|
|
|
|
Boss, My db server is running in windows box... |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: How to reset/restart/modify the IDENTITY counter?
|
|
|
Who.Really.Really.Cares@gmail.com wrote:
| Quote: | Serge Rielau wrote:
*sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time
you
catch up with 3 years worth of fixpack....
I'm running DB2 V7.1 for z/OS. (in original post I wrote 7.2 but it
proved to be a mistake).
PS: Makes me think just how much money I could make as a consultant
instead of a salary-man
I'm not sure what you're trying to say. Your smart procedure uses
"ALTER TABLE ... ALTER COLUMN ... RESTART WITH ..." syntax, which is
not available to me. So your reply doesn't fit to my problem, I guess.
What I was trying to achieve was to simulate "RESTART WITH"
functionality.
WhoReallyCares
Ah.. now that starts to make sense. |
I just checked DB2 V8 for zOS and RETART is supported.
You may want to:
* bug your boss when you can get DB2 V8
* bug your IBM Rep and ask whether the feature can be made abvailable
on DB2 V7. I can't imagine you being the first to need it.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Civilian_Target
*nix forums beginner
Joined: 26 May 2005
Posts: 3
|
| Posted: Thu May 26, 2005 5:53 pm Post subject:
Re: Explanation/help with DB2 memory Usage on Linux?
|
|
|
Problem cured. The app I'm using was trying to open more connection
pools than the DB had to offer.
Civilian_Target
Civilian_Target wrote:
| Quote: | Getting this same error on 8.1FP7a using Windows2000, if its any help to
you. It only seems to occur when the system is under quite heavy stress,
dealing with more than 50 concurrent connections. The error is slightly
different, it mentions nothing about memory allocation failure. Perhaps
it might be an idea to increase your DBM heapsize, or the heap
allocation for that database.
Civilian_Target
PaulR wrote:
Hi,
We have a Server running SLES 8 and 3GB memory, with 1 DB2 instance and
2 active Databases.
General info...
DB2level = "DB2 v8.1.0.72", "s040914", "MI00086", and FixPak "7"
uname -a = Linux galahad 2.4.19-64GB-SMP #1 SMP
/etc/sysctl.conf
kernel.shmmax=268435456
kernel.msgmni=1024
kernel.sem="250 32000 32 1024"
From my bufferpool allocations etc.. I reckon I should be using about
1GB of the 3GB available, but I am getting memory allocation errors
e.g from db2diag.log...
2005-01-26-16.10.15.117133+000 I173528014G494 LEVEL: Severe
PID : 4941 TID : 1024 PROC : db2agent
(JBSUKS23)
INSTANCE: db2jbsi1 NODE : 000 DB : JBSUKS23
APPHDL : 0-87 APPID: C0A86313.AC04.00A286161018
FUNCTION: DB2 UDB, base sys utilities, sqleSubsequentConnect, probe:70
RETCODE : ZRC=0x850F0005=-2062614523=SQLO_NOSEG
"No Storage Available for allocation"
DIA8305C Memory allocation failure occurred.
When I look at 'top' I see...
Mem: 3104908K av, 2988836K used, 116072K free, 0K shrd,
89496K buff
Swap: 1052248K av, 279436K used, 772812K free
2000040K cached
When I sum the vmdata of all /proc/nnn/status (idea found on another
forum) i.e grep -i vmdata /proc/$i/status | cut -f 2 ...
I get a total of
1,247,372 KB
When I look at ipcs, i.e ipcs -m , and sum the shared memory segments,
I get
821,274,526 Bytes
So all seems to indicate around 1GB which is what I expected, so why
memory allocation problems?
Any pointers/explanation would be great.
Thanks.
Paul.
|
|
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Tue Dec 02, 2008 5:39 am | All times are GMT
|
|
Car Loans | Credit Report | Online Advertising | Bad Credit Mortgages | Secured Loans
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
