Anyone know any DB2 ?

If anyone has any DB2 knowledge could they check out some of the questions posted in the Database Developers Lounge.

Thanks,


Mary


http://www.msdner.com/forum/forum155.html

Quote:

Originally Posted by mmccarthy

If anyone has any DB2 knowledge could they check out some of the questions posted in the Database Developers Lounge.

Thanks,


Mary

DB2 has now become an official database forum and can be found at

http://www.msdner.com/forum/forum159.html

Mary

anyone imported text file generated from DB2?

Has anyone import into SQL Server a text file generated by DB2?

the DB2 files has bunch of weird characters in it that look like they are used for formatting...as opposed to actually being data.

Also, SQL can't find a row terminator.

I received a cobol declaration copy book for the data, but I have no idea what to do with the special characters.
Any help would be appreiciated.

There's a sample of the copybook...

* COBOL DECLARATION FOR TABLE @.TNJ00.PL_JUDGM_ATTORNEY *
************************************************** ****************
01 DCLPL-JUDGM-ATTORNEY.
10 PJATY-ATTORNEY-KEY.
15 PJATY-JUDGMENT-ID PIC X(14).
15 PJATY-GROUPING-CDE PIC S9(4) USAGE COMP.
15 PJATY-ROLE-CDE PIC X(1).
88 PJATY-CREDITOR VALUE 'C'.
88 PJATY-DEBTOR VALUE 'D'.
88 PJATY-TRUSTEE VALUE 'T'.
15 PJATY-GROUP-SEQ PIC S9(4) USAGE COMP.
15 PJATY-ENTRY-SEQ PIC S9(4) USAGE COMP.
10 PJATY-NAME-PREFIX PIC X(4).
10 PJATY-LAST-NME PIC X(25).
10 PJATY-FIRST-NME PIC X(12).
10 PJATY-MIDDLE-NME PIC X(12).
10 PJATY-NAME-SUFFIX PIC X(4).
10 PJATY-CITY-NME PIC X(25).
10 PJATY-STATE PIC X(2).
10 PJATY-ZIP-CDE PIC X(9).
10 PJATY-TELEPHONE PIC X(10).
10 PJATY-LST-MNT-DTE PIC S9(9) USAGE COMP.
10 PJATY-REC-LOCK-CDE PIC X(1).
10 PJATY-ALT-NAME.
49 PJATY-ALT-NAME-LEN PIC S9(4) USAGE COMP.
49 PJATY-ALT-NAME-TEXT PIC X(75).
10 PJATY-ADDR-LINE-1.
49 PJATY-ADDR-LINE-1-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-1-TEXT PIC X(50).
10 PJATY-ADDR-LINE-2.
49 PJATY-ADDR-LINE-2-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-2-TEXT PIC X(50).
10 PJATY-ADDR-LINE-3.
49 PJATY-ADDR-LINE-3-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-3-TEXT PIC X(50).
************************************************** ****************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 20 *
************************************************** ****************Tell them to send you another file and unpack the numeric fields, and to make the sign a separate character.

The "USAGE COMP" (usage computational) packs a digit into 4 bits, so that each byte contains two digits. Then COBOL also uses a half byte for the sign unless they specify sign separate and it's location (trailing or leading).

PIC 9(04) would mean numeric values occupying 4 bytes.
PIC 9(04) usage comp would mean numeric values occupying 2 bytes
PIC S9(04) usage comp means numeric values occupying 3 bytes (2 for the numbers and one for the sign with the sign leading the numeric values.


The 01 level is a record level descriptor. The 10 is a field. the 15 are the sub-fields that make up the field if the 10 level does not have a type identifier. All PIC X fields are fixed length. The 88 level describes the allowed values in the 15 field just above them. The 49 level is the same as the 15 level ... it describes the data in the sub-fields that make up the 10 field.

Clear as mud, i'm sure. As for the row terminator, you have to calculate the row length and apply your own. You might be able to import this with DTS, but it would be easier to have them send it to you as all character data, them you import and convert to numeric (all numbers in this example are integers (thank goodness) ... but couls have positive or negative signs).|||Very good...I thought I was the last salt on the planet that knew this stuff...

In either case, not only do you have that, you also have varchar's it looks like...which shouldn't be a problem.

Also, you don't know if any of the columns are nullable.

What you really need is the LOAD Card that is generated by DB2 when they unload the data. THAT will tell you what you have, not the COBOL Copybook.

TYhey should probably unload the data using PARM('SQL') and use SQL DML to creat a nice loadable file|||164 150 141 156 153 040 171 157 165 040 166 145 162 171 040 155 165 143 150

in octal

(http://nickciske.com/tools/octal.php)|||You will get CRLF in the file. I've never needed to generate a row terminator...in any event, the Column names are definetly not the column names in DB2...they are limited to 18 bytes...another reason to get the LOAD CARD

You want them to do this

//UNLOAD JOB (B,X,XXXXX),'UNLOAD',PRTY=13,GROUP=XXXXXXXX,
// NOTIFY=&SYSUID,MSGCLASS=V,TIME=60
//*+JBS BIND XTDDBB4.ONLINE
//UNCAT EXEC PGM=IEXUNCAT,COND=(4,LT)
//SYSIN DD *
XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606
/*
//UNLOAD EXEC PGM=IKJEFT01,REGION=6M,COND=(4,LT)
//STEPLIB DD DSN=BXXXB4.DB2.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD DUMMY
//SYSREC00 DD SPACE=(CYL,(100,25),RLSE),
// UNIT=DASD,DISP=(,CATLG),LABEL=RETPD=365,
// DSN=XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606
//SYSPUNCH DD DUMMY
//*SYSPUNCH DD DISP=SHR,
//* DSN=XXXXXX.DBA.DBB4.SBD000DB.CTLCARD(INDEX)
//SYSTSIN DD *
DSN SYSTEM(DBB4)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('BXXXB4.DB2.RUNLIB.LOAD') PARMS('SQL')
END
/*
//SYSIN DD *
SELECT PJATY_JUDGMENT_ID
, CHAR(PJATY_GROUPING_CDE,8)
, PJATY_ROLE_CDE
, CHAR(PJATY_GROUP_SEQ,8)
, CHAR(PJATY_ENTRY_SEQ,8)
, PJATY_NAME_PREFIX
, PJATY_LAST_NME
, PJATY_FIRST_NME
, PJATY_MIDDLE_NME
, PJATY_NAME_SUFFIX
, PJATY_CITY_NME
, PJATY_STATE
, PJATY_ZIP_CDE
, PJATY_TELEPHONE
, CHAR(PJATY_LST_MNT_DTE,18)
, PJATY_REC_LOCK_CDE
, CHAR(PJATY_ALT_NAME_TEXT,75)
, CHAR(PJATY_ADDR_LINE_1_TEXT,50)
, CHAR(PJATY_ADDR_LINE_2_TEXT,50)
, CHAR(PJATY_ADDR_LINE_3_TEXT,50)
FROM TABLE
/*|||And if you really want to smoke their minds...ask them for a comma delimted file

SELECT ' "'||COALESCE(RTRIM(PJATY_JUDGMENT_ID),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUPING_CDE,8)),'')| |'"'
||',"'||COALESCE(RTRIM(PJATY_ROLE_CDE),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUP_SEQ,8)),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_ENTRY_SEQ,8)),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_NAME_PREFIX),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_LAST_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_FIRST_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_MIDDLE_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_NAME_SUFFIX),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_CITY_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_STATE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ZIP_CDE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_TELEPHONE),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_LST_MNT_DTE,18)),'')| |'"'
||',"'||COALESCE(RTRIM(PJATY_REC_LOCK_CDE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ALT_NAME_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_1_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_2_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_3_TEXT),'')||'"'
FROM TABLE|||And if you already have a file based on that layout...you can't use it...or at least you can't use it with out A LOT of coding on your end

Any structure on Sql Server like WITH ... SELECT structure on DB2

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

......................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (codechar(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
MemduhIn SQL Server, you can use SELECT ... INTO to create a table using a SELECT
query as the source for schema and data. Untested example:

SELECT
code,
SUM(amount)
INTO #totals
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code

SELECT
code,
SUM(amount)
FROM #totals
GROUP BY code

You can also produce the result using a derived table instead of a temp
table:

SELECT
code,
SUM(amount)
FROM
(
SELECT
code,
SUM(amount) AS amount
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code
) AS totals
GROUP BY code

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Memduh Durmaz" <mdurmaz@.derece.com.tr> wrote in message
news:e0c9cfd5.0408291350.31b8585a@.posting.google.c om...
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh|||On 29 Aug 2004 14:50:25 -0700, Memduh Durmaz wrote:

> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code

You can always use a derived table:

SELECT code, SUM(amount)
FROM (
SELECT code, SUM(amount) FROM trans1 GROUP BY CODE
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY CODE
) AS TOTALS
GROUP BY CODE

(the "AS" in "AS TOTALS" is optional, and is often omitted.)|||SQL-Server 2005 will support Common Table Expressions (the WITH ...
SELECT stuff). If you don't want to wait a year, then you would have to
work around in SQL-Server 2000, or get hold of a beta-version.

HTH,
Gert-Jan

Memduh Durmaz wrote:
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh

--
(Please reply only to the newsgroup)