Speeding Up QUIZ with Suprtool
Contents
1. READER gt get D SALES gt if PRODUCT WIDGET gt sort CUST ACCOUNT gt output SALESUB erase gt xeq IN 20 OUT 6 CPU Sec 1 Wall Sec 1 Once you have created a PowerHouse subfile with the correct structure For Techies you can use Suprtool to select records from the datasets sort them and then write them to the subfile By default the Output command creates a new file You can overwrite the contents of an existing subfile by using the Erase option which overwrites the data records without affecting the mini dictionary in the file labels If you want to add records to the end of a subfile without overwriting the existing records use the Append option References Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Step 3 QUIZ can now read the subfile m Change the QUIZ report from gt access D SALES gt select if PRODUCT WIDGET gt sort on CUST ACCOUNT gt Heading to gt access SALESUB gt sorted on CUST ACCOUNT gt Heading m QUIZ TIP Compiled QUIZ program doesn t require mini dictionary 8 Now you can change the QUIZ report to read the subfile instead of the For Techies dataset First remove the Select statement from the QUIZ report because Suprtool has its own set the subfile contains only qualified records Since Suprtool has already of high speed sort sorted the records you also have to replace the Sort command with routines that are Sorted generally2. external data for QUIZ This tutorial will examine the following topics Suprtool subtotal and count operations e Why use Suprtool with QUIZ e How can Suprtool and QUIZ work together to reduce system load and report times e How can you create complex linkages and link files on non key fields e Techniques to create new subfiles and new fields in subfiles e How can you create subfiles for data that isn t described in your PowerHouse dictionary e How can you use Suprtool s powerful Total and Count functions with QUIZ Suprtool is a trademark of Robelle Solutions Technology Inc For Techies References Speeding Up QUIZ with Suprtool Why preselect data with Suprtool Suprtool features a Fast and efficient serial reads of files Powerful and flexible selection features a Efficient sort routines a Links files on any field with minimum disc I O a Interfaces with many application tools m QUIZ features Powerful flexible report writer Uses standard data retrieval methods Suprtool is a pure bred performance product It excels at reading files For Techies serially selecting qualified records and delivering them to application programs as efficiently as possible QUIZ is a report writer It is easy to use and it provides a rich set of report formatting features Unfortunately in many cases QUIZ spends most of its time reading data that will be discarded because it doesn t meet the selection criteria of a r
3. much faster than the HP sort QUIZ TIP QUIZ doesn t need the mini dictionary to execute a compiled report It only checks the record length of the input file which can be an ordinary MPE disc file References Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Linking multiple data files Using Suprlink with PowerHouse Suprtool reads and selects records from each data file Suprtool sorts the qualified records on the link field into flat files m Suprlink links the files into one record and writes to the subfile m QUIZ does the final reporting In the real world you probably have to collect report data from different For Techies input files Once Suprtool has extracted the data you can use Suprlink to merge the selected fields into one subfile Gathering report data from multiple files involves the following steps 1 Determine which fields are required for the final report create a subfile for the Suprlink output 2 Use Suprtool to extract records from each of the contributing data files sort them on the link field and then write them to self describing files 3 Use Suprlink to merge the extracted files into one large record write the Suprlink output to a subfile 4 Report the subfile with QUIZ References Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Links on key fields One to many links a Link fiel
4. of the records If QUIZ has to do indexed reads on a significant number of records Choose instead of Select in many cases it is more efficient to read the whole file serially with Suprtool References Speeding Up QUIZ with Suprtool Step 1 Creating subfiles mw QUIZ gt access D SALES gt report summary all gt set subfile name SALESUB keep size 10000 gt set report limit 1 gt go m QTP gt access D SALES gt subfile SALESUB keep size 10000 include D SALES gt set input limit 0 gt go m Different results if items redefined more than once You can use either QUIZ or QTP to create a subfile QTP has two features that can make it a better choice 1 The record length of a QUIZ subfile can be greater than the original data records because fields with multiple redefinitions appear more than once The record length of a QTP subfile is exactly the same as the original data records 2 QTP s syntax is more flexible With QTP it s easier to reference fields from two datasets to create a subfile for Suprlink output You can also use an alias to include items with the same name Suprtool is a trademark of Robelle Solutions Technology Inc For Techies You have to specify the size of the subfile otherwise it defaults to the process limit for the QUIZ or QTP run References Speeding Up QUIZ with Suprtool Step 2 Populate the subfile with Suprtool a run SUPRTOOL PUB ROBELLE gt base STORE 5
5. output file would contain the same records as the QUIZ extract but the sequence of the fields would be different References Since QUIZ repeats the link field in its output file the length of the output record is the sum of the lengths of the two data records Suprtool is a trademark of Robelle Solutions Technology Inc 11 11 Speeding Up QUIZ with Suprtool Replacing QUIZ with Suprlink m Change one to many links to many to one output file cannot contain more records than input file m Field sequence is different from QUIZ output m Link field is not repeated in output record record length of Suprlink output file is smaller than QUIZ Optional linkage defaults fields to blanks or zeros A Suprlink output file cannot have more records than the input file For Techies because it contains only one copy of the link field To replace the intelligent but slow QUIZ link with a dumb but fast Suprlink you have to make several changes Since Suprlink can only link the first qualifying record the file with many qualifying records should drive the task If you change the input file the new output file would contain the same records as the QUIZ subfile it replaces but the sequence of the fields would be different If linkage is optional and there is no match Suprlink assigns blanks and zeros to the link record fields in the output file QUIZ uses the data dictionary to assign item defaults References Suprtool is
6. Speeding Up QUIZ with Suprtool Speeding Up QUIZ with Suprtool A Robelle Tutorial Hans Hendriks August 1996 Copyright 1996 Robelle Solutions Technology Inc ijk robelle QUIZ from Cognos is a fine report writer Suprtool is an excellent For Techies performance tool This tutorial aims to teach you how and when to integrate these two tools to dramatically decrease the run time of slow reports This tutorial focuses on interfacing Suprtool with QUIZ and provides only brief explanations of Suprtool examples If you would like to learn more about Suprtool commands and techniques please attend our Suprtool Master Class My name is Hans Hendriks I ve been a technical support consultant at Robelle since 1993 Before joining Robelle I worked as a Cognos and Robelle distributor for many years and have been a Suprtool user for 8 years References For further information on topics covered in this tutorial please consult the Suprtool User Manual Robelle Solutions Technology Inc Toll free 1 888 762 3553 Suite 201 15399 102A Avenue Telephone 604 582 1700 Surrey B C Canada V3R 7K1 Fax 604 582 1799 E mail support robelle com WWW http www robelle com QUIZ is a trademark of Cognos Corp Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool What s Inside Why use Suprtool Combining Suprtool and QUIZ Linking multiple files Creating complex subfiles Importing
7. a trademark of Robelle Solutions Technology Inc 12 12 Speeding Up QUIZ with Suprtool Debugging tip m First create a self describing SD file with the LINK option gt output SALCUST LINK m Use FORM command to examine record structure gt form salcust File SALCUST HANS TECHSUP SD Version B 00 00 Entry Offset CUST ACCOUNT Z8 1 lt lt Sort 1 gt gt DELIV DATE I2 9 PRODUCT NO Z8 13 PRODUCT PRICE T2 2L PURCH DATE I2 25 POSTAL CODE X6 135 Limit 108 EOF 8 Entry Length 140 Blocking 29 13 When programming a Suprtool and Suprlink front end for a complex For Techies QUIZ report it is critical that the subfile you create exactly matches the record structure of the Suprlink output record This means you need to specify fields in the correct sequence when you create the subfile To ensure that your subfile dictionary maps exactly onto the Suprlink output record it is useful to let Suprlink create a self describing file by using the Link option instead of Erase gt input salefile gt link custfile gt output salcust LINK Then you can use the Form command to examine the file s record structure References 13 Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Creating subfiles from multiple datasets m Indexed link QUIZ gt Access D SALES link to M CUSTOMER gt report summ
8. ant 15 Because Suprtool can link files in non index fields it is sometimes For Techies impossible for QUIZ to perform the same logical linkage to create the subfile In these cases the following steps can build a subfile with the correct field sequence and characteristics from any two files that contain the required fields 1 Create a one record subfile of each file 2 Link the subfiles on record number 3 Create a single subfile with the required fields from the two smaller subfiles It is not important that the two files used to be logically related only that they contain the fields required to create a mini dictionary which correctly describes Suprlink s output records References 15 Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Creating subfiles without a PowerHouse dictionary m Create a one record subfile with QUIZ gt define NAME FIRST character size 10 gt define NAME LAST character size 20 gt define DELIV DATE Integer size 4 0 gt report summary all gt set subfile name m Ensure data types match actual data a Integer 4 isnotthe same as Integer size 4 a Check record length of subfile against data 16 If you need to report data that isn t declared in your PowerHouse For Techies dictionary you can use the following technique to create a subfile Although there is no from scratch Access statement QUIZ 1 Use QUIZ Define commands to assign forma
9. ary CUST ACCOUNT NAME FIRST amp gt NAME LAST PURCH DATE gt set subfile name QTP gt Access D SALES link to M CUSTOMER gt subfile SALFILE size 10000 keep amp gt include D SALES NAME FIRST NAME LAST gt set input limit 0 gt go m Suprlink does not repeat link field in output record Because Suprlink does not repeat the link field in the output file you For Techies must take care when creating the subfile for Suprtool output Note that in this example we have specified the required fields explicitly Alternatively you can change the Suprlink output file so that it resembles the QUIZ result it replaces You can do this by repeating the link field in the Suprtool task that creates either the link or the input file gt get m customers gt define custact2 cust account gt extract city cust account custact2 cust status gt output References Suprtool is a trademark of Robelle Solutions Technology Inc 14 14 Speeding Up QUIZ with Suprtool Creating complex subfiles without an indexed link m Suprlink can link files on any field m QUIZ requires an index to link m How do you create a QUIZ subfile with the required fields m Two steps 1 Build a one record subfile of each data file 2 Link subfiles on record number to create new subfile gt Access SALSUB link to record 0 of CUSTSUB gt report summary CUST ACCOUNT NAME FIRST m Subfiles must have correct item definitions data not import
10. d appears twice in subfile m Suprlink a Links flat files on any sorted field Each input file record can generate only one output record a Link field appears once in output record 10 QUIZ and Suprtool use a fundamentally different approach to linking For Techies QUIZ relies on indexes to link data For example Access M CUSTOMER link to D SALES performs indexed reads of the sales dataset for each record in m customer and then it links each customer record to all the qualifying sales records This linking strategy of one to many can result in numerous redundant disc I Os especially if the detail records are not stored contiguously and the file has a small blocking factor QUIZ creates a subfile that contains all the fields selected from both datasets including two copies of the link field Suprlink takes a completely different approach First the input and link files must be sorted on the same field Then Suprlink reads the files concurrently and tries to link each record in the input file with the first References qualifying record in the link file As a result Suprlink only reads each input file once using efficient NOBUF MR reads Suprtool can link on non key fields but it cannot perform one to many links This prevents the output file from containing more records than the input or driver file Unlike QUIZ Suprlink does not repeat the link field in the output file 10 Suprtool is a trademark of Robelle Solutions Tec
11. e commands m Use Suprtool FORM command to examine structure a Use Show Items in QUIZ to display structure Carefully examine your requirements Call our toll free number if you need help In conclusion the record structure of the Suprtool output file and the For Techies QUIZ subfile must be identical when you are integrating these two tools Carefully examine your report requirements and the subset of records being extracted before you preselect data with Suprtool and Suprlink If you re not sure how to proceed call our toll free Technical Support number 1 888 762 3553 7 a m to 4 p m Pacific time Monday to Friday Pager service available for after hours emergencies References Suprtool is a trademark of Robelle Solutions Technology Inc 19 19
12. eport You can dramatically improve the run time performance of reports that select less than 50 of the data by replacing QUIZ data selection with Suprtool s more efficient serial reads References Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Reading input files Suprtool can read IMAGE datasets KSAM files MPE disc files Tape files a Other files with fixed length records m QUIZ can read QDD or PDL declared files PowerHouse subfiles m MPE disc files can be declared in the PowerHouse dictionary Suprtool can read almost any type of MPE iX file IMAGE datasets For Techies KSAM files MPE disc files tape files as well as most files with fixed length records It can sort summarize link and reformat the data and direct its output to many different file types Since QUIZ must know the record structure of a file to report its contents it can only read two types of files 1 Files declared in the QDD or PDL dictionary 2 Self describing files specifically PowerHouse subfiles Most PowerHouse sites have their regular production data files defined in their PowerHouse dictionary Typically this includes some MPE disc files such as archived data or report data extracted from larger files If References you declare them in the PowerHouse dictionary the files don t have to be redefined every time they are accessed You can also use file equations to assi
13. gn the same logical filename to different physical files at execution time Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool A typical QUIZ and Suprtool task m Choose an input method for QUIZ Suprtool cannot create subfiles 1 Create an empty subfile or 2 Describe a direct or sequential file in PDL or QDD m Use Suprtool to populate the file Access the output file in QUIZ and link to others Integrating Suprtool and QUIZ boils down to using Suprtool to extract the required data and present it to QUIZ in a format it can understand For regularly scheduled extracts it may be easier to describe the output file in your PowerHouse dictionary Otherwise you can follow these three steps 1 Use QUIZ or QTP to create an empty subfile with the required structure 2 Use Suprtool to read the data files select the required records sort them if necessary and write them to the PowerHouse subfile 3 Change the QUIZ report to access the subfile instead of the original data files remove the selection criteria and the sort commands If you use these tools together each product can do what it does best Suprtool can read and retrieve the data and QUIZ can process and format the qualified records Suprtool is a trademark of Robelle Solutions Technology Inc For Techies Since QUIZ still needs to read the subfile you should only use Suprtool if you are selecting less than 50
14. hnology Inc Speeding Up QUIZ with Suprtool Linking with Suprlink versus QUIZ M CUSTOMER File D SALES File CUST ACCO NAME FIRST NAME LAST PURCH DATE GUST ACEO 10001 Darlene Hamilton T993 1015 10003 10002 Gordon Lackner TIJS 10003 10003 John Melander 199305 10003 10008 Thomas Serge sim 19931020 10010 10009 Gordon Oxenbury TIIS 10016 10010 Wayne Humphreys BIOS OZ TOOLE 10011 William Kirk 199100 10020 10012 Berney Ferguson 199 5 T028 10020 10013 alien Andersen 10019 Rupert Hillstrom 10020 Walley Nisbet m QUIZ links 6 records 14 records if optional link CUST ACCOUNT NAME FIRST NAME LAST PURCH DATE CUST ACCOUNT E Suprlink links 3 records 11 records if optional link CUST ACCOUNT NAME FIRST NAME LAST PURCH DATE In this example Suprlink reads each customer record in the input file and For Techies links it to the first matching sales record in the link file If linkage is optional and there is no match Suprtool writes the input customer record to the output file and initializes the link record fields to blanks or zeros QUIZ reads each record in the Access file and links it to every matching record in the link file If linkage is optional and there is no match the link record fields are initialized to dictionary defaults With Suprlink you should create many to one links instead of one to many By changing the input file the Suprlink
15. ry values Create subfile with sort and total fields Calculate sort break totals with Suprtool gt get d sales gt sort cust account gt dup none keys total sales total gt extract cust account gt out saltot erase m Use totals in QUIZ report gt access saltot link to d sales gt define d pcnt num 3 amp sales total st total 1 100 gt report The most common requirement for multiple pass reports in QUIZ For Techies involves summary values count total average that have to be calculated in the first pass so that the result can be used in the second In a sales report for example you may wish to show the percentage that each line contributes towards a customer s total amount This is easily achieved by using Suprtool to prepare a subfile of subtotals which QUIZ can link back to the original data files This subfile need only contain the subtotal and the sort field which is used for linking back to the data file There is a useful side effect to having a sorted subfile when the records are linked back to the data file the record complexes will be built in sorted order References Suprtool is a trademark of Robelle Solutions Technology Inc 18 18 Speeding Up QUIZ with Suprtool P Summary of Speeding Up QUIZ Suprtool delivers qualified data to QUIZ Data must be in a format QUIZ understands Use PowerHouse to create its own data structures a ra Create new items with QUIZ Defin
16. ts to fields and peel Benen ates ane i aai f output record determine their sequence initialize character fields to P blanks and numeric fields to zeros 2 Direct output to a subfile specify the required size 3 Use Suprtool to populate the file with data References Suprtool is a trademark of Robelle Solutions Technology Inc Speeding Up QUIZ with Suprtool Creating new data fields m Suprtool can summarize at sort breaks gt duplicate none keys total Sales total Suprtool creates new fields for totals a Field names ST TOTAL 1 ST TOTAL 2 etc Appended to record a Field format P28 packed decimal m To create a compatible field in QUIZ gt define D Total packed size 14 0 One case where you need to define new fields involves Suprtool For Techies summary functions gt duplicate none keys total sales amt The totals are created in P28 format and appended to the end of the output record You can also use Suprtool to count records at sort breaks gt duplicate none keys count Suprtool creates a new four byte integer field for the count value which can be defined in QUIZ as follows gt define numcust integer size 4 0 References When you use the Define command in the process of creating the subfile QUIZ allocates space for the new field and assigns it a logical name Suprtool is a trademark of Robelle Solutions Technology Inc 17 17 Speeding Up QUIZ with Suprtool A typical requirement Summa
Download Pdf Manuals
Related Search