View in COBOL in place of physical file

[voicucosmin90]

Hi guys,
I have a question if you can help me please, I have to use a view created with SQL in place of the physical file, I created the view with sql and I don't have dds for this:

create view library.file_view
as select*from library.pysical_file
where some conditions (some_filed of physical_file ='xx' for example)

I have to change the physical file with the view file in a few cobol programs, but I think I don't have key records for this, I supposed that the view is similar to logical file,
the physical file has 2 fields as keys and it is declared as:

select physical_file to database-physical_file
organization indexed
access mode dynamic
record key is externally-described-key
file status physical_file-status.

Please if you can help me with this, I have to make a logical file with dds with declared key?

[TedHolt]

Re: View in COBOL in place of physical file

A view cannot be keyed. If I can't change the program to use embedded SQL, I create a logical file.

Another technique that I have tried but not used in production is to create an index that has conditions and includes all fields, and change my program to read that index as a database file. It works in RPG test programs, but whether it's a good idea or not, I don't know.

Also, I have only tried this for input-only files. I don't know what would happen if a program tried to update or add rows (records) thru the index.

create index qiws/qcustcdt1
on qiws/qcustcdt (cusnum)   
where state in ('TX', 'CA') 
rcdfmt cusrec               
add all columns

[tomliotta]

Re: View in COBOL in place of physical file

The question doesn't say that SQL can't be used in the COBOL program; it just says that the view should be used. Of course, if it can be a SQL COBOL program, it doesn't seem to make clear sense that a view was created with all columns from the table unless it is intended to authorize users only to certain rows in the table. And if that's the case, then creating a LF with DDS contradicts requirements.

It seems that this needs to be a SQL COBOL program and that keys are irrelevant. Identification of rows is simply by appropriate uses of WHERE clauses. The rows could be FETCHed through a cursor over the view and UPDATEd when the identifying columns match whatever supposed to match. These would be done using a WHERE CURRENT OF <cursor> clause. Or UPDATEs will simply be done directly to the view, again while matching identifying columns. Matches in this case would be a simple WHERE clause.

More needs to understood about requirements and about programs that need to be changed before being sure of much.

[B.Hauser]

Re: View in COBOL in place of physical file

Since a view never has key and you apparently want to use joined view and read the data in a predefined sequence, you must use embedded SQL instead of native I/O.

Birgitta

[TedHolt]

Re: View in COBOL in place of physical file

Maybe I don't understand the question, voicucosmin90.

As I understand it, you have a lot of programs that access a physical file by key. You need to change those programs so that they only access certain records of the file. You've created a view to select the records and you want to make the programs use the view instead of the physical file. If so, then you need a logical file or index that is keyed like the physical file and will select the records.

If that's not accurate, please correct me so I can understand.

[voicucosmin90]

Re: View in COBOL in place of physical file

Thanks for answers guys,.. yes, TedHolt, this is it, I have to change the physical file in these programs with the view (view created with SQL for retrieving with some conditions records from physical file). The problem was that I don't have keys on the view and I don't know how to handle this stuff in cobol programs, how can I change statements:
select physical_file to database-physical_file
organization indexed
access mode dynamic
record key is externally-described-key
file status physical_file-status.

... I will try to make a logical file with DDS with keys in place of view, I will see if I will can make on this way.

[TedHolt]

Re: View in COBOL in place of physical file

I brought this thread to the attention of Dan Cruikshank of IBM. He said creating an index with the RCDFMT and ADD ALL COLUMNS phrases and declaring it for record-level access was a bad idea. He recommended creating a logical file.

[voicucosmin90]

Re: View in COBOL in place of physical file

Thank you TedHolt, I created a logical file, I chose this option with a logical file for selection and keys, thanks for help.

[tomliotta]

Re: View in COBOL in place of physical file

If that's the case, then the original statement "I have to use a view created with SQL" seems to be wrong. And if a keyed logical will be used in place of the physical, maybe you could have simply added a key to the physical and skipped creating a logical.

In many cases, a key can be added with the ADDPFCST TYPE(*PRIKEY) command. It does require uniqueness, but that's not unusual. If unique keys are needed, it can be handy.

[voicucosmin90]

Re: View in COBOL in place of physical file

thanks tomliotta, yes, finally I used a logical file and initially I was wrong when I said a view,.. the idea was to retrieve just some records from the physical file,.. the physical file was indexed and had 2 keys and in the program it is used indexed with keys.
I made a logical file with these two keys in order to keep the program logic and the access to the file.. but in the DDS for logical file I have set selection only for the records who accomplish some conditions (for example select only records with some_field = 'X', (this was the scope for the SQL view but I don't know how to handle this in cobol and I rest to logical file with DDS which is good) )