Announcement

**matt.stucky** · January 24, 2005, 11:03 AM

ok i figured part of it out....

i found the STRSQL utility and the CREATE INDEX function... which leads me to the following questions:

do i need to explicitly identify an index to use in my query, or does DB2 pick the best one? is that the same answer for an index (or LF) created with EITHER sql or the CRTLF command?

the reason i ask is this: the customer I am currently at does NOT have STRSQL installed... and wont install it.

if i need to explicitly use a LF, what is the syntax (from within SQL)?

thx

matt

**arrow483** · January 24, 2005, 01:08 PM

SQL vs DDS

SQL can't create a LF.

Short description: SQL indexes and views exist on the AS400(iSeries) much as they do in any other database.

LF are a "combined" view + index. They are usually created with DDS, which are the basic spec.s for building files.

Since you don't know DDS, it would be faster to CREATE INDEX, and yes, DB2 will pick the "best" index.

And, since you don't have STRSQL, use Ops Navigator (aka iSeries Navaigator). Open "databases" and your database (usually the system name). Use the option at the bottom of the screen "Run a SQL script". This will open a window for the SQL statement. Enter and run from there.

**matt.stucky** · January 31, 2005, 02:20 PM

so i might be screwed then....

...should I find myself at a client that does NOT have STRSQL or Ops Nagrivator available? if that were the case, then is it true that my only option to create an index would be with the CTRLF command? if so, one of the parameters is the library/file that has the create table DDS specification, and if I don't know that then I can't use CRTLF to create an index on the table. si o no?

**jamief** · January 31, 2005, 03:29 PM

I think I get your question

You can find the library of the physical by using:

Lets say the file name is "WKARTNO"

[code[
WRKOBJ *ALL/WKARTNO[/code]

Gives you this

Code:

 Type options, press Enter.                                                    
   2=Edit authority        3=Copy   4=Delete   5=Display authority   7=Rename  
   8=Display description   13=Change description                               
                                                                               
 Opt  Object      Type      Library     Attribute   Text                       
      WKARTNO     *FILE     AUTOPRO     PF          Daily Routing              
      WKARTNO     *FILE     GMCFIL      PF          Daily Routing              
      WKARTNO     *FILE     LBIFIL      PF          Daily Routing              
      WKARTNO     *FILE     LCFFIL      PF          Daily Routing              
      WKARTNO     *FILE     PDMFIL      PF          Daily Routing              
      WKARTNO     *QRYDFN   PHILLIB     QRY                                    
      WKARTNO     *FILE     TEMP        PF          Daily Routing

You can then use this command

Code:

dspdbr lbifil/wkartno

You get this

Code:

    1/31/05              Display Data Base Relations                      
 DSPDBR Command Input                                                     
   File  . . . . . . . . . . . . . . . . . . . : FILE       WKARTNO       
     Library . . . . . . . . . . . . . . . . . :            LBIFIL        
   Member  . . . . . . . . . . . . . . . . . . : MBR        *NONE         
   Record format . . . . . . . . . . . . . . . : RCDFMT     *NONE         
   Output  . . . . . . . . . . . . . . . . . . : OUTPUT     *             
 Specifications                                                           
   Type of file  . . . . . . . . . . . . . . . :            Physical      
   File  . . . . . . . . . . . . . . . . . . . :            WKARTNO       
     Library . . . . . . . . . . . . . . . . . :            LBIFIL        
     Member  . . . . . . . . . . . . . . . . . :            *NONE         
     Record format . . . . . . . . . . . . . . :            *NONE         
     Number of dependent files . . . . . . . . :                8         
 Files Dependent On Specified File                                        
   Dependent File         Library       Dependency   JREF    Constraint   
       WKARTNOA           LBIFIL        Data                              
       WKARTNOC           LBIFIL        Data                              
       WKARTNOD           LBIFIL        Data                              
       WKARTNOG           LBIFIL        Data                      
       WKARTNOF           LBIFIL        Data                      
       WKARTNOB           LBIFIL        Data                      
       WKARTNOE           LBIFIL        Data                      
       WKARTNOH           LBIFIL        Data

Then you have to find where the source comes from that creatd the logical.

Code:

DSPOBJD OBJ(LBIFIL/WKARTNO) OBJTYPE(*FILE) DETAIL(*SERVICE)

I have written tools to do this they are here....

http://www.code400.com/find.php

and

http://www.code400.com/ffd.php

hope this helps
Jamie

**arrow483** · January 31, 2005, 06:02 PM

CRTLF

Well... iSeries Navigator is almost universal. But there are people who haven't installed it (even tho its free).

Well to create at LF, find the Library and Source File, then create a new member. The DDS for a simple LF might look like this:

A************************
A* STEEL SURCHARGE TABLE
A*************************
A R SURCHARGEF
A PFILE(SURCHARGE)
A
A K COMPANY
A K CUSTNO
A K ITMCLASS
A K ASOFDATE DESCEND
A

The "A" is in column 6. Anything with a * in col 7 is a comment. In this example, the physical file is named SURCHARGE - the PFILE, the record format in the physical file is named SURCHARGERF (on the "R" line).

The K lines are the keys.

**matt.stucky** · February 1, 2005, 10:49 AM

refering to jamie's post, 2 above....

ok i think i get what you're doing jamie.

with the WRKOBJ call, does *ALL really mean all libraries, or just the ones in the library list?

and, with your final point, the call to dspobjd, i am assuming there you mean to call a LF, not the orignal PF that started this whole thing. is that right? so you find the PF, display dependent files (which an LF most certainly is) then you can display the LF. i'll buy that fer a dollar.

so moving on....

i tried to create a LF on a small PF i have, to no avail. Need some help on the correct parameters for the CRTLF call.

i used STRPDM to create a small, 3-field table. it also only has 3 recrods in it. here is the dds:

Columns . . . : 1 71 Edit DIDATA/DDSSRC
SEU==> EMPPF
FMT PF .....A..........T.Name++++++RLen++TDpB......Functi ons++++++++++++++++++
*************** Beginning of data *************************************
0001.00 A R EMPPFR
0002.00 A EMPNO 9A
0003.00 A SALARY 7P 0
0004.00 A BIRTHDATE L
****************** End of data ****************************************

and here is the results of STRSQL: select * from didata/emppf:

Display Data
Data width . . . . . . : 32
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3..
EMPNO SALARY BIRTHDATE
222334444 30,000 08/01/04
555667777 20 09/01/67
999009999 1,000,000 01/01/50
******** End of data ********

so you can see, so far so good.

so I did a CRTLF with an F4, then took my best guess at the params:

Create Logical File (CRTLF)

Type choices, press Enter.

File . . . . . . . . . . . . . . mylogicalf Name
Library . . . . . . . . . . . didata Name, *CURLIB
Source file . . . . . . . . . . ddssrc Name
Library . . . . . . . . . . . didata Name, *LIBL, *CURLIB
Source member . . . . . . . . . emppfr Name, *FILE
Generation severity level . . . 20 0-30
Flagging severity level . . . . 0 0-30
File type . . . . . . . . . . . *DATA *DATA, *SRC
Member, if desired . . . . . . . *FILE Name, *FILE, *NONE
Physical file data members:
Physical file . . . . . . . . *ALL Name, *ALL
Library . . . . . . . . . . Name, *CURRENT
Members . . . . . . . . . . . Name, *NONE
+ for more values
+ for more values

my objective is to create an LF/index on the 3rd column, the date of birth.

but it won't create the LF... I don't know what I am doing wrong. DDSSRC (not Q..) is the DDS defn of the table. in there is one record type (member) that is named EMPPFR (employee physical file record).

so what am i doing wrong?

**arrow483** · February 1, 2005, 11:03 AM

format

I guess I lost formatting when posting the sample.

The A should be in col 6. The R in col 17, the name (format or field) in col 19 and PFILE in col 45. In a LF you don't need field sizes or types.

SEU==> EMPPF
FMT PF .....A..........T.Name++++++RLen++TDpB......Functi ons++
0001.00 A R EMPPFR
0001.05 A PFILE(EMPPF)
0002.00 A EMPNO
0003.00 A SALARY
0004.00 A BIRTHDATE

If you put these spec's in DIDATA/DDSSRC member MYLOGICALF

CRTLF FILE(DIDATA/MYLOGICALF) SRCFILE(DIDDATA/DDSSRC)

The FILE parm refers to where the new logical is to be built, the SRCFILE refers to the library/file where the SOURCE code has been entered. The Source member defaults to the name of the file being created (in this case MYLOGICALF) and is only needed if the source member name doesn't match.

**matt.stucky** · February 1, 2005, 03:10 PM

getting closer

i had to use STRPDM to find the name of the "member". the command

CRTLF FILE(DIDATA/MYLOGICAL) SRCFILE(DIDATA/DDSSRC) SRCMBR(EMPPF) DTAMBRS(
(DIDATA/EMPPF (BIRTHDATE)))

gives:

Errors in DDS not allowed with specified GENLVL. +

what the hell does that mean?

and, am I indexing the Birthdate column correctly? In the previous example, no specific column is given, so is it indexing on all columns in the table? or is an index on a specific column not the way DB2 works?

matt

**jamief** · February 1, 2005, 03:14 PM

Post your DDS

Matt post your DDS for physical and logical and I can help YA

Jamie

**matt.stucky** · February 1, 2005, 03:57 PM

did i fall off the boat here somewhere?

i thought I posted my DDS 4 posts above... for the PF.

and i can't post my logical because that's what I'm trying to create.

i was using the same DDS for both the PF and LF... because i understood DDS just to define a record. is that not correct? do i need to create a DDS for the LF the same way I created it for the PF? is that where i missed the bus?

ya?

**jamief** · February 1, 2005, 04:06 PM

Yes you need to have source for both

You dont think im actually reading this whole post do Ya ????

Yes you need source for both............

Het click the DDS link (in brown) on the navigation bar to your right.

Code:

Physical file

 
     A*
     A*
     A          R C40SELLR
     A*
     A            STATUS        01          COLHDG('Status')
     A*
     A            LOCATION      04          COLHDG('Location')
     A            CUSTOMER      11          COLHDG('Customer#')
     A            CUSTOMERNM    30          COLHDG('Customer Name')
     A*
     A            STATE         02          COLHDG('State')
     A            ZIPCODE       05          COLHDG('ZipCode')
     A            MSA           04          COLHDG('MSA Code')
     A*
     A            DATE            L         COLHDG('Sale_Date')
     A*
     A            SALESMAN      03          COLHDG('Salesman')
     A            SALESMANNM    30          COLHDG('Salesman Name')
     A*
     A*
     A            ACCOUNTGRP    04          COLHDG('ACCOUNTGRP')
     A*
     A            CODPRODUCT    20          COLHDG('COD_Prod')
     A            C4PRODUCT      33          COLHDG('Code400_Prod')
     A            SPRODUCT      28          COLHDG('C4S-Short')
     A*
     A            PRODGROUP     05          COLHDG('Product Group')
     A            PRODCAT1      05          COLHDG('Product Cat 1')
     A            PRODCAT2      05          COLHDG('Product Cat 2')
     A*
     A* Commision by sales price 3
     A*
     A            UNIT$         11  4       COLHDG('Unit_Price')
     A            COMMISSION    09  2       COLHDG('Commission')
     A*
     A*  Commission by average
     A*
     A            UNIT$2        11  4       COLHDG('AVG_Price')
     A            COMMISSIO2    09  2       COLHDG('Avg_Commission')
     A*
     A            UNITCOM       06  3       COLHDG('Unit Commission')
     A            QUANTITY      05  0       COLHDG('How_Many')
     A*
     A            EXTENDED      10  2       COLHDG('Extended_Sales')
     A*
     A            YEARMONTH     06  0       COLHDG('YearMonth')
     A*
     A*
     A          K RPRODUCT




Logical file



     A          R C40SELLR                  PFILE(C40SELLP)
     A*
     A          K RPRODUCT
     A*
     A          S STATUS                    COMP(EQ 'E')

There are examples in there of a physical and logical file.

Take care
Jamie

**matt.stucky** · February 1, 2005, 04:27 PM

amp it down. are you on crack? :P

i have no clue what you just told me.... really, you've run past me at 1000 mph....

here is where i am.

i think i need a separate DDS for both the PF and LF. ok, i can get that part.

strpdm
work with members
f6 create
name: emplf
type: lf

name type? S? so then I put in the field name, birthdate and that seems to do it. ya?

so now i have 2 members in ddssrc. when i use the new member in the CRTLF command, same error message.

my dds for the LF:

Columns . . . : 1 71 Browse DIDATA/DDSSRC
SEU==> EMPLF
FMT LF .....A..........T.Name++++++.Len++TDpB......Functi ons++++++++++++++++++
*************** Beginning of data *************************************
0001.00 S BIRTHDATE
****************** End of data ****************************************

**matt.stucky** · February 2, 2005, 10:00 AM

@#%@#%@#%@#%. ever heard the term "bring your own condom and vaseline"?

ok, so here below is my dds for both my PF and my LF.

STRPDM
3 work with members gives:

Code:

                           Work with Members Using PDM                 S104BMZM

 File  . . . . . .   DDSSRC
   Library . . . .     DIDATA               Position to  . . . . .

 Opt  Member      Type        Text
      EMPLF       LF          index on birthdate
      EMPPF       PF          employee master

so then edit the PF (emppf)
and that shows:

Code:

 Columns . . . :    1  71            Edit                         DIDATA/DDSSRC
 SEU==>                                                                   EMPPF
 FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++
        *************** Beginning of data *************************************
0001.00      A          R EMPPFR
0002.00      A            EMPNO          9A
0003.00      A            SALARY         7P 0
0004.00      A            BIRTHDATE       L
        ****************** End of data ****************************************

and edit on the LF displays:

Code:

 Columns . . . :    1  71            Edit                         DIDATA/DDSSRC
 SEU==>                                                                   EMPLF
 FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++
        *************** Beginning of data *************************************
0001.00                 R EMPLF                     PFILE(DIDATA/EMPPF)
0002.00                 K BIRTHDATE
0003.00                 S SALARY
0004.00                 S EMPNO
        ****************** End of data ****************************************

so I think what I have to do with my dds is to specify a new R type that Keys on Birthdate, but also Selects the other columns i want in my view. so in this way, the view and the index are one. Zen DB2. that makes sense, i guess.

so i tried a 14=compile on the LF and see nothing new. and when i do a CRTLF i still get the GENLVL error.

wham wham wham. (sound of head hitting desk).

any help?

matt

**jamief** · February 2, 2005, 10:11 AM

Your logical would look like this

Code:

 Columns . . . :    1  71            Edit                         DIDATA/DDSSRC
 SEU==>                                                                   EMPLF
 FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++
        *************** Beginning of data *************************************
0001.00                 R EMPPFR                     PFILE(EMPPF)
0002.00                 K BIRTHDATE
****************** End of data ****************************************

Thats it create it with the CRTLF command or just use option 14 in PDM

Announcement

newbie question: index on DB2 file

newbie question: index on DB2 file

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment