how to configure a LF which does a select by single character from field

[chris_hird]

Here is the SQl I am trying to build a Logical file for.

SELECT * FROM IFSAUDF WHERE ATTR like 'd%' and SYS = 'SRC' and FTYPE = 'M' ORDER BY PATH;

Here is my attempt.

     A          R IFSREC                    PFILE(IFSAUDF)
                  PATH
                S SYS                       COMP(EQ 'SRC')
                  ATTR                      COMP(GE 'd')
                  FTYPE                     COMP(EQ 'M')

ATTR is a 10 character field so I just want to select where the first character is a 'd', will the above do that for me? I am not sure about converting SQL 'like' into DDS ??

Chris...

[B.Hauser]

Re: how to configure a LF which does a select by single character from field

DDS described logical files are an outdated technology.
Create SQL indexes instead (they are much more powerful than logicals and can be used with native I/O like any DDS described logical file):

CREATE INDEX YourSchema.YourIndex ON YourSchema.YourTable 
      (PATH) 
       WHERE Attr like 'd%' and SYS = 'SRC' and FTYPE = 'M';

For SQL queries, indexes cannot be specified, but the query optiomizer needs them to get fast access to the data.

If you need to access the same data in multiple SQL statements and do not want to repeat the where condition over and over again.
Create an SQL View:

Create View YourSchema.YourView
as (Select * from YourSchema.YourTable
       WHERE Attr like 'd%' and SYS = 'SRC' and FTYPE = 'M');

In either way never ever specify a DDS described logical file in an SQL statement. The query optimizer needs to rewrite the query based on the underlying phyiscal files/tables.
Before release 7.1 TR 5 all queries where a DDS described logical file was specified were rerouted to the OLD/Classic Query Engine (CQE) and could not profit from the advantages of the new SQL Query Enigine (SQE).
Note: SQL Query Engine NOT DDS Query Engine

Birgitta

Birgitta

[GLS400]

Re: how to configure a LF which does a select by single character from field

Hi Chris:

If you must use dds....
1. list each field to be included in the dds
2. add a field which is a substring of attr
3. add the new field in the select/omit portion

     A          R MYFILEF                   PFILE(MYFILE)    
     A            ATTR          10A         TEXT('ATTR')    
     A            ATTR1            I        SST(ATTR 1 1)
     A            ADCCOD         1A         TEXT('ADD/DELETE/CHANGE')
     A          K ATTR1          
     A          K NOTYCD                                 
     A          O HLDRLS                    CMP(NE 'R')  
     A          S ATTR1                      VALUES('D')

Best of Luck
GLS

[chris_hird]

Re: how to configure a LF which does a select by single character from field

Birgitta, I agree I should be using SQL all the way. Unfortunately I am only just starting to use embedded SQL and have a number of concerns, the biggest being the lack of documentation for language specific usage. I posted a request for help on a C program which would not compile, I finally figured out that I needed to use the CRTSQLCI command and then CRTPGM! Try finding that in the docs! I use SQL exclusively in PHP and node.js etc etc, but trying to find suitable tutorials for C has not been successful to-date, so if you know any any redbooks etc I should read please let me know.

GLS, thanks for your input, makes a lot of sense as I had looked at SST but not really figured out how to implement.

Chris...

[chris_hird]

Re: how to configure a LF which does a select by single character from field

     A          R MYREC                     PFILE(IFSAUDF)                           150121                             
     A            SYS            3A         TEXT('SYSTEM')                           150121                             
     A            PATH        5002A         TEXT('Path Name')                        150121                             
     A            ATTR          10A         TEXT('Attributes')                       150121                             
     A            ATTR1          1A         SST(ATTR 1 1)                            150121                             
     A            FTYPE          1A         TEXT('ERROR TYPE')                       150121                             
     A          S SYS                       COMP(EQ 'SRC')                           150121                             
     A            ATTR1                     COMP(EQ 'd')                             150121                             
     A            FTYPE                     COMP(EQ 'M')                             150121                             
                K PATH                                                               150121

Does not compile has lots of errors.

Even though embedded SQL is not well documented I do have it working as expected.

Would like to know what I have done wrong though so please keep the comments coming, may need it in the future :-)

Chris...

[GLS400]

Re: how to configure a LF which does a select by single character from field

Just glanceing at your dds:
remove the 1A and add Use field of I for the attr1 field

Best of Luck
gls

[chris_hird]

Re: how to configure a LF which does a select by single character from field

GLS, thanks it compiled..

Chris..

[DAG0000]

Re: how to configure a LF which does a select by single character from field

Unfortunately I am only just starting to use embedded SQL and have a number of concerns, the biggest being the lack of documentation for language specific usage. I posted a request for help on a C program which would not compile, I finally figured out that I needed to use the CRTSQLCI command and then CRTPGM! Try finding that in the docs! I use SQL exclusively in PHP and node.js etc etc, but trying to find suitable tutorials for C has not been successful to-date, so if you know any any redbooks etc I should read please let me know.

Chris,

There is no lack documentation for anything on this system. This is something I think IBM has always been very good at providing. Trying to locate what you need can be difficult though. I think this is due to there being so much of it and a lot of it is not what you could call user friendly. It took me a while before I was able to begin understanding API's because I was not fully understanding anything based upon the way IBM's had them documented.

[tomliotta]

Re: how to configure a LF which does a select by single character from field

Try finding that in the docs! I use SQL exclusively in PHP and node.js etc etc, but trying to find suitable tutorials for C has not been successful to-date, so if you know any any redbooks etc I should read please let me know.

There is quite a bit of info in the Coding SQL statements in C and C++ applications topic in the Information Center. It can be found by drilling down through Database-> Programming-> Embedded SQL programming. Not sure how many of your questions would be easily found, but there are numerous examples.

[chris_hird]

Re: how to configure a LF which does a select by single character from field

Tom,

I actually looked at all of those links and still struggled to find suitable samples and tutorials. The samples are the compiler listings with some notes so no real explanation about what each part of the program does and why it is coded the way it is. I also looked at the various header files to see if I could work out what was going on, it wasn't until I came across some old code of mine that I wrote for another project where I found that I was using the wrong compile command.

I am always looking for better ways to do things and I am sure embedded SQl is going to be a technology I can use, I just can't see how best to implement it at the moment.

Chris...

[tomliotta]

Re: how to configure a LF which does a select by single character from field

The Preparing and running a program with SQL statements is a sub-topic that follows the language-specific topics. Info about the commands, etc., should be in that area. But I agree it's not easy to pull it all together, especially for C on this platform. C isn't a very high priority for embedded SQL.

Tom

[B.Hauser]

Re: how to configure a LF which does a select by single character from field

But I agree it's not easy to pull it all together, especially for C on this platform. C isn't a very high priority for embedded SQL.

Even though you'll find more often examples in C than in RPG (at least within the newer IBM i documentation, articles, white papers etc.)
Just have a look at: Database SQL XML Programming

You'll find there code snippets in C, Java, COBOL and even PLI, but not a single example in RPG!!!

Birgitta