Toad World® Forums

DB2 memory problem


#1

When running queries against a DB2 database, TOAD uses the machine memory and
this can cause TOAD to crash if the memory runs out.

This was a problem in an early version of TOAD for Data Analysts, but seemed to
have been resolved. However, it has returned


#2

Hello,

Can you let us know the version of TDA you are running where the memory issue
returned? Is it with the latest 2.7.0.89 beta or have you noticed it previously?

Also, do you use DB2 exclusively or do you see the same thing when running
queries against other platforms (Oracle, SQL Server or ? )? Can you send a
sample query and give us an idea of the amount of data it returns (rows/columns/
types)? Thanks.

Alan


#3

Does TDA have the ability to export a result set as an insert statement? Or is
this in Toad for Oracle?

Tad


#4

Use the export type of ‘Sql Script’. This will export as inserts.
You can also include the table DDL if you like.

Debbie


#5

I was able to create the Inserts. Thank You

Questions
image001.jpeg


#6

I was able to create the Inserts. Thank You

Questions
image001.jpeg


#7

I am not getting either of the behaviors you mention. What connection type are
you using and what version of TDA?


#8

I am using an odbc connection to the iSeries db2/400 v6r1m0 with TDA Beta
2.7.0.89.
image001.jpeg


#9

I am using an odbc connection to the iSeries db2/400 v6r1m0 with TDA Beta
2.7.0.89.
image001.jpeg


#10

I have to wait until next week to test this. I’ll let you know what I
find.

Debbie


#11

Thanks
image001.jpeg


#12

Thanks
image001.jpeg


#13

[ Attachment(s) from Debbie Peabody included below]

I am now connected to iSeries and get the DDL option for SQL Script export type
in both TDA 2.6 and 2.5. See attached. What version are you using of TDA?

Debbie


#14

I am using an odbc connection to the iSeries db2/400 v6r1m0 with TDA Beta
2.7.0.89.


#15

Two people have checked this and we both see the ‘Include DDL’
option when exporting in the SQL Script option. Can you send a full screenshot.
Maybe we can see something in that.

Debbie


#16

insert into SCRATCH.BNASH1_PUR_PROMO_1 (

select a.ENTITY || a.BRANCH || ‘00’ || a.PRODUCT_CD || a.ACCOUNT_NO as
account_ref_20,

a.ACCOUNT_REF as account_ref_16,

a.BRANCH || a.PRODUCT_CD || a.ACCOUNT_NO as account_ref_14,

a.ENTITY,

a.BRANCH,

a.PRODUCT_CD,

a.ACCOUNT_NO,

a.BDP_CUSTOMER_NO,

a.OPEN_MTH,

a.OPEN_DT,

a.PRODUCT_TYPE_CD,

a.PRODUCT_SUB_CD,

a.SUB_PRODUCT_CD,

a.RELATIONSHIP_CD,

a.CROSS_SALE,

a.CHANNEL_CD,

a.CHANNEL,

a.COMPANY,

a.BT_F3M_IND,

a.CLOSE_DT,

a.CLOSE_REASON_CD,

a.GUARANTEED_FLAG,

b.PU_FINAL_PRM_STMT_DT,

b.BT_FINAL_PRM_STMT_DT,

c.INITIAL_ECO_CON,

d.E6416_VATRAFIN as PU_PROMO_PERIOD,

f.E6416_VATRAFIN as BT_PROMO_PERIOD

from SCRATCH.VINTAGE_ACCOUNT a

left join SCRATCH.VINTAGE_RATE b

on a.account_ref = b.account_ref

and a.open_mth = b.month_dt

left join SCRATCH.VINTAGE_XPAN c

on a.account_ref = c.account_ref

left join ( select distinct

E6416_CDGSUBP,

E6416_CONPROD,

E6416_VATRAFIN

from ST_HCO_EC_PPACK

where E6416_CDCONECO = 23

and E6416_VATRAFIN

on a.SUB_PRODUCT_CD = d.E6416_CDGSUBP

and c.INITIAL_ECO_CON = d.E6416_CONPROD

left join ( select E6416_CDGSUBP,

E6416_CONPROD,

max(E6416_CONTCUR) as E6416_CONTCUR

from ST_HCO_EC_PPACK

where E6416_CDCONECO = 24

and E6416_VATRAFIN

group by E6416_CDGSUBP,

E6416_CONPROD) e

on a.SUB_PRODUCT_CD = e.E6416_CDGSUBP

and c.INITIAL_ECO_CON = e.E6416_CONPROD

left join ( select distinct

E6416_CDGSUBP,

E6416_CONPROD,

E6416_VATRAFIN,

E6416_CONTCUR

from ST_HCO_EC_PPACK

where E6416_CDCONECO = 24

and E6416_VATRAFIN

on a.SUB_PRODUCT_CD = f.E6416_CDGSUBP

and c.INITIAL_ECO_CON = f.E6416_CONPROD

and e.E6416_CONTCUR = f.E6416_CONTCUR

where open_mth between ‘01/06/2008’ and ‘01/12/2009’)

with ur;

commit;

VINTAGE_ACCOUNT is distinct on account_ref and contains 1m rows

VINTAGE_RATE is distinct on account_ref, month_dt and contains 1m rows per month
(18 months)

VINTAGE_XPAN is distinct on account_ref and contains 1m rows

ST_HCO_EC_PPACK is a lookup table with approx 7k rows

Using most recenbt of the beta versio of toad.

It does not affect sql server as the code ran ok

This query should return approximately 500k rows


#17

Thanks for sending the information. For clarification did this just start happening with the latest beta? The reason we’d like to confirm it wasn’t occuring either in the 2.7.0.65 beta or in the 2.6 GA release is that we updated the DB2 client software in 2.7.0.89. We’ll be able to narrow our focus if we know with some certainty it just started with 2.7.0.89. Do you have a GA or trial copy of TDA 2.6 to confirm whether or not you get the same results as in the current beta (if you haven’t already)?

Thanks.
Alan


#18

It has affected both 2.7 beta version. I cannot recall this affecting version
2.6 beta version, but I am not entirely sure.