A Review Paper On Feature Selec-Tion Methodologies And Their Applications IBM ISeries AS/400 SQL Performance

You are searching about A Review Paper On Feature Selec-Tion Methodologies And Their Applications, today we will share with you article about A Review Paper On Feature Selec-Tion Methodologies And Their Applications was compiled and edited by our team from many sources on the internet. Hope this article on the topic A Review Paper On Feature Selec-Tion Methodologies And Their Applications is useful to you.

IBM ISeries AS/400 SQL Performance

1. Introduction

Recently I made a few tests at a client’s site on SQL performance with large volumes of data. I found quite interesting results that I summarise below.

2. Application Description

The client is a financial company which stores large volumes of data for a Basel application which provides a month by month situation of the contracts. The data are used for some standard reporting and also for non standard OLAP inquiries requested especially by Credit control users

The application updates 7 different physical files which are joined together in a join LF called BASUNICO1L which has a total record length of 1789 characters. The main keys of this file are the processing period (YYYYMM) and the contract number

3. Test Environment

The tests were made in a test environment where the files contained the data of about 6 months. The total number of records for the join LF were approximately 11 million and those of the period used for the test were about 2 millions.

The first set of tests were made to test the times needed to copy all records of one period from the join logical file to a physical file with the same record layout. The record were written by using three different approaches as follows:

A simple copyfile (CPYF) which includes a selection such as INCREL(*IF PERIOD *EQ 200806)

A traditional (file oriented) Cobol program (TSTFIL1) which used a START to position the file pointer to the first record of the period and then entered into a loop of READ and WRITE operations to write to the output file all records of the requested period.

A Cobol program with embedded SQL (TSTSQL1) which wrote the output records with a simple SQL Insert of the selected records.

A Cobol program with embedded SQL (TSTSQL3) which created the output file as an SQL MQT table. A second set of test was used instead to test the times needed to read some fields of the files by using different approaches as follows:

Traditional file oriented Cobol program (TSTFIL5) which performed a START to position the cursor and then used sequential READ operations of all records of the selected period. Cobol program with embedded SQL (TSTSQL5) which included an SQL cursor to read all lines of the selected period. I decided to perform the tests in the following different conditions:

– In an environment without additional indexes (i.e only the access paths of the files)

– In an environment that could use also additional SQL indexes

– In an environment that used an SQL logical view instead of the original join logical file

– The results are described in the following points

4. Tests based only on the join logical file

The test made in the original environment to copy about 2 million records without SQL indexes were the following:

– The CPYF took 19 minutes and 35 seconds

– The traditional file oriented Cobol program took 9 minutes and 20 seconds

– The Cobol program with embedded SQL took 28 minutes

5. Tests done after the creation of SQL indexes

I checked the index advisor file QSYS2/SYSIXADV after the initial tests and I noticed that there was a record suggesting to create an index on the period field. I created both a vector index and a radix index and then started the new set of tests. The results were the following:

– The CPYF took 7 minutes and 52 seconds

– The traditional file oriented Cobol program took 7 minutes and 7 seconds

-The Cobol program with embedded SQL took 13 minutes and 20 secons

– There has been a clear benefit especially for the Cobol program with embedded SQL. By loooking at the log, I noticed that the optimiser had chosen to use the vector index during that execution.

It is interesting that if you use the commands WRKOBJ or DSPFD or DSPDBR, the indexes appear as logical files with a special SQL type attribute which has the value INDEX.

It is also interesting that the space used for the indexes is much less than what is required for a logical file. In my test environment one of the main the PF used about 500 Mega bytes, a LF used about 257 Mega bytes, the radix index used occupied about 175 Mega bytes and the radix index only 22 Mega bytes.

6. Test of the file creation with as an MQT (Materialised query table)

I tried to create the target file by using a Cobol program with embedded SQL that produced the output table as an MQT instead of the previous INSERT and I found that the time were very good

The program took just 57 seconds to produce the results.

7. Reading Test

I compared the time spent by two programs to read from all records of a selected period to sum up a total The first program was a Cobol traditional file oriented program, whereas the second one was an SQL Cobol which included an SQL cursor to read all selected records.

The first program took 13 minutes and 17 seconds to complete, whereas the second one took on the first execution just 7 minutes and 14 seconds to complete and much less in following ones.

8, Using SQL Views instead of Logical files

I tried to use an SQL view equivalent to the logical file and repeat the tests above by using the SQL view. The results were not significantly different than those based on the logical file, however I found that a view required much less space than an equivalent LF.

The SQL views appear again to the system as special logical files with the SQL type attribute containing the value VIEW. The space occupied by the view logical files is much less than that required by a logical file. In my test environment a view equivalent to a join logical file of about 405 Mega bytes. required less than 1 Mega byte.

9. Conclusions

The tests described above seem to demonstrate what follows:

The fastest option to extract the data was the Cobol program with an embedded SQL to create a MQT table.

The traditional file oriented Cobol program was faster than the corresponding Cobol program which included an embedded SQL INSERT statement. The execution time of the Cobol SQL program was significantly affected by the creation of the SQL vector index.

The traditional file oriented Cobol program that read all records of a selected period by using START and READ NEXT operations was slower than the Cobol program which read the records with an SQL cursor.

The results agree with some points of an IBM Redbook Modernizing IBM eServer iSeries Application Data Access – A Roadmap Cornerstone ( redbooks.ibm.com/abstracts/sg246393.html?Open ). where it is written that:

The SQL insert operations are slower than Cobol write statement because SQL operations include more validations than write oprrations into a DDS PF SQL does faster reads than HLL operations. The main reason is that a cursor reading an SQL table does not have the extra data cleansing code like a DDS PF reading. Using SQL views instead of logical files should allow a significant reduction of the space occupation. The final conclusion is that a wise use of SQL can bring significant improvements in applications performance.

Video about A Review Paper On Feature Selec-Tion Methodologies And Their Applications

You can see more content about A Review Paper On Feature Selec-Tion Methodologies And Their Applications on our youtube channel: Click Here

Question about A Review Paper On Feature Selec-Tion Methodologies And Their Applications

If you have any questions about A Review Paper On Feature Selec-Tion Methodologies And Their Applications, please let us know, all your questions or suggestions will help us improve in the following articles!

The article A Review Paper On Feature Selec-Tion Methodologies And Their Applications was compiled by me and my team from many sources. If you find the article A Review Paper On Feature Selec-Tion Methodologies And Their Applications helpful to you, please support the team Like or Share!

Rate Articles A Review Paper On Feature Selec-Tion Methodologies And Their Applications

Rate: 4-5 stars
Ratings: 6807
Views: 86665420

Search keywords A Review Paper On Feature Selec-Tion Methodologies And Their Applications

A Review Paper On Feature Selec-Tion Methodologies And Their Applications
way A Review Paper On Feature Selec-Tion Methodologies And Their Applications
tutorial A Review Paper On Feature Selec-Tion Methodologies And Their Applications
A Review Paper On Feature Selec-Tion Methodologies And Their Applications free
#IBM #ISeries #AS400 #SQL #Performance

Source: https://ezinearticles.com/?IBM-ISeries-AS/400-SQL-Performance&id=1883437

Related Posts

default-image-feature

A Review On The Features And Progress Of Dual-Ion Batteries Dell Latitude 3480 Overview of an Affordable, Easy to Use 14-Inch Laptop for Business Users

You are searching about A Review On The Features And Progress Of Dual-Ion Batteries, today we will share with you article about A Review On The Features…

default-image-feature

A Review Of Cross-Cultural Research On Job Satisfaction Concluded That Outsourcing Smartly To Cut Costs And Keep Your Reputation for Quality

You are searching about A Review Of Cross-Cultural Research On Job Satisfaction Concluded That, today we will share with you article about A Review Of Cross-Cultural Research…

default-image-feature

A Job Performance Review Is A Form Of Two-Way Communication Interviewing Tips for Employers – Getting the Right People on the Bus

You are searching about A Job Performance Review Is A Form Of Two-Way Communication, today we will share with you article about A Job Performance Review Is…

default-image-feature

A Good Place For Reviewers To Start Is With Answers.Com Rocket Chinese Course Review

You are searching about A Good Place For Reviewers To Start Is With Answers.Com, today we will share with you article about A Good Place For Reviewers…

default-image-feature

A Century In Review A Decade-By-Decade Social And Historical The Adventure Of English by Melvyn Bragg

You are searching about A Century In Review A Decade-By-Decade Social And Historical, today we will share with you article about A Century In Review A Decade-By-Decade…

default-image-feature

9 What Are The Primary Parts Of A Peer-Reviewed Article The Science of Mother Love

You are searching about 9 What Are The Primary Parts Of A Peer-Reviewed Article, today we will share with you article about 9 What Are The Primary…