Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Formation of SQL query using ICETOOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
hernikiten

New User


Joined: 08 Apr 2005
Posts: 23
Location: india

PostPosted: Sat Mar 17, 2007 10:34 pm    Post subject: Formation of SQL query using ICETOOL
Reply with quote

Hi

I have a file with as below


'010000017',
'010000034',
'010000035',
'010000037',
'010000049',
'010000050',
'010000063',
'010000067',
'010000085',
'010000087',
'010000100',
and so on 1750000 records.

Now I want to create an sql like this

Select * from policytab where
policynumber IN (
'010000017',
'010000034',
'010000035',
'010000037',
'010000049',
so on till 450 records of above file....
.........
.......
);
Select * from policytab where
policy number IN (
another 450 records
);

and so on.....
The total number of records is not a multiple of 450. After the last record only ); needs to be attached.

Is it possible?
Thanks in advance.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Mar 17, 2007 11:04 pm    Post subject:
Reply with quote

Hello,

For what it is worth, i'd suggest that you write a simple bit of COBOL that would read your "policy number" qsam file and for each policy#, issue the "select * from the policytab". When the row is retrieved, create whatever output you need. If the policy number does not exist, skip it, report it, or abend as best suits your situation.

You will have complete control in the COBOL code. If you get the sort to generate what you want, you still have to run al of the SQL queries and deal with an unpredictable (per the posted requirement) number of queries and outputs.

If you are concerned that a single run would be too big, you can include logic in the code to start and stop at runtime porovided first/last policy numbers or by actual count (i.e. start at zero; stop at 450). If you set up the code to process "groups" you do not want to hard-code them.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sat Mar 17, 2007 11:10 pm    Post subject:
Reply with quote

It's possible. Without DFSORT, probably not as neatly.....
With sort you could treat the select as a header and the closing paren as a trailer. The only problem with that is that the max pagesize is only 255.
Another way might be by using an inrec sequence number incrementing by 2 and control break on the 4th digit, 500 to group.
Since you have ICETOOL, I'd wait to see what magic Frank will perform. icon_smile.gif
Back to top
View user's profile Send private message
hernikiten

New User


Joined: 08 Apr 2005
Posts: 23
Location: india

PostPosted: Sun Mar 18, 2007 12:23 am    Post subject:
Reply with quote

Dick, William
Thanks for your comments.
I started it with a single query, but realized that system supports around 450 values in parenthesis(IN statement).
Trouble is that there is one to many situation here. Which would demand a cursor, if I go for a cobol module. Considering the bulk of data, I think that would make it bit inefficient.
In the above situation as well, the multiple data sets created for multiple queries will be a problem.

Cheers
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Mar 18, 2007 12:51 am    Post subject:
Reply with quote

hernikiten wrote:
In the above situation as well, the multiple data sets created for multiple queries will be a problem.
If I understand, a GDG for each output and using the base to read all would simplify things there.
BTW, isn't the limit closer to 750?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Mar 18, 2007 1:39 am    Post subject:
Reply with quote

Hello,

With 1.75 million records, the number of entries per "IN" won't much matter. If there is a one-to-many, the amount of selected rows increases.

Using a cursor and selecting the "many" will not be much of a performance issue if the rows returned are determined by the policy# key.

If you use code rather than sort, you can start/stop wherever you want. As Bill suggests, you could "stack" the outputs in a gdg for use later.

For ease of implementation and ease of subsequent enhancement/modification if this requirement grows or another opportunity presents itself, i'd recommend you use "real" code. Also, i'd imagine that if you wrote the COBOL, you would be running in a couple of hours. . . . A SELECT/ASSIGN, an FD, get the start/stop control if used, a file OPEN, a file READ, a cursor DECLARE, a FETCH, an output write for each row FETCHed, end process.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sun Mar 18, 2007 9:42 pm    Post subject:
Reply with quote

hernikiten,

What is the RECFM and LRECL of your file?

I don't know SQL, so please tell me exactly what the end of each query will look like:

'nnnnnnnnn',
);

or

'nnnnnnnnn'
);

or something else?

William said
Quote:
The only problem with that is that the max pagesize is only 255.


I think you're referring to the limit of 255 for LINES=n, but with REMOVECC, LINES=n isn't relevant.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Mar 18, 2007 9:54 pm    Post subject:
Reply with quote

Good catch, the end of each query should not have the trailing comma.

Without LINES, how would I cause "page" headers and footers to generate?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sun Mar 18, 2007 10:12 pm    Post subject:
Reply with quote

Quote:
Without LINES, how would I cause "page" headers and footers to generate?


I actually have a solution in mind that uses SECTION headers rather than page headers, so REMOVECC could be used. and LINES=n wouldn't matter I'll wait until hernikiten tells me the RECFM and LRECL before I see if I can work it out.
Back to top
View user's profile Send private message
hernikiten

New User


Joined: 08 Apr 2005
Posts: 23
Location: india

PostPosted: Mon Mar 19, 2007 2:25 am    Post subject: Re: Formation of SQL query using ICETOOL
Reply with quote

Hi Frank et al,

An FBA format and record length of 40 will do. Last value record per query won't have a comma trailing.

Cheers
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Mar 19, 2007 9:19 pm    Post subject:
Reply with quote

hernikiten,

Here's a DFSORT job that will do what you asked for. Since you said the input file has RECFM=FBA, I assumed the first character was the ANSI carriage control character and the first apostrophe starts in position 2. I also assumed that you wanted the output file to be FB/40 without the ANSI character. I used a division by 450 trick to give each group of 450 records the same value so we could use SECTIONS on that value. I removed the comma from the last record of each group using a modulo 450 trick, and I removed the comma from the last record of the file using a symbol for the sequence number of the last record. Quite tricky, but it does work.

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FBA/40)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS),
//  RECFM=FB
//SYM DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN    DD    *
  OPTION COPY
  INREC IFTHEN=(WHEN=INIT,BUILD=(2,39,X)),
     IFTHEN=(WHEN=INIT,OVERLAY=(41:SEQNUM,8,ZD,START=0,
     49:41,8,ZD,DIV,+450,TO=ZD,LENGTH=8,
     57:41,8,ZD,MOD,+450,TO=ZD,LENGTH=5))
  OUTFIL FNAMES=T1
  OUTFIL FNAMES=SYM,REMOVECC,NODETAIL,
    BUILD=(1,48,80:X),
    TRAILER1=('LASTRCD,+',41,8)
/*
//S2    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=&&T1,DISP=(OLD,PASS)
//SORTOUT DD DSN=...  output file (FB/40)
//SYSIN    DD    *
  OPTION COPY
  OUTFIL REMOVECC,
    IFOUTLEN=40,
    IFTHEN=(WHEN=(57,5,ZD,EQ,+449,OR,41,8,ZD,EQ,LASTRCD),
      OVERLAY=(12:X)),
    SECTIONS=(49,8,
      HEADER3=('Select * from policytab where',/,
               'policynumber IN ('),
      TRAILER3=(' );'))
/*
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Fri Mar 23, 2007 4:54 pm    Post subject: Re: Formation of SQL query using ICETOOL
Reply with quote

Frank u r great...........
Hats off 2 u.........
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Multiple VSAM files single output fil... Mohan Kothakota DFSORT/ICETOOL 11 Wed Aug 09, 2017 7:57 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us