IBM Mainframe Forum

[diptisaini]

SORT BY ACAS-CAMAST.ACTION-TYPE ACAS-CAMAST.OLD-ASSET-ID
USING ACAS-CAMAST.CA-NO ACAS-CAMAST.OLD-ASSET-DESC
ACAS-CAMAST.ISSUER-NAME #CAMAST-ISN

Please tell me the meaning of above syntax and how it will work in natural.

[BillyBoyo]

Have you tried the manual? Searching on the internet?

What is the particular problem you have with the statement?

Usually "SORT BY" ("usually" as in I don't know about Natural) would be the keys that you are asking the data to be sequenced on, so here you would have two keys.

[diptisaini]

Here i am not able to understand the meaning of USING clause . I mean we are sorting according to a key which we have mentioned after sort by statement so what this using will do?

[BillyBoyo]

"The USING clause indicates the fields which are to be written to intermediate sort storage. It is required in structured mode and optional in reporting mode. However, it is strongly recommended to also use it in reporting mode so as to reduce memory requirements. "

This is from the manual, which I found with Google "Adabas Natural Sort Using" and then did a full-text-search from there.

[RGZbrog]

The SORT statement is not limited to sequential file processing. In addition to WORK file records, Natural can sort Adabas records and even internal tables. In fact any data in the DEFINE DATA areas can be sorted. For example, you can populate a table of color codes, then sort the table. In effect, the fields in the table are the "sort record." To let Natural know this, you name those fields in the USING clause.

In Reporting Mode, much of Natural's processing is implicit. Natural figures things out for you. So when you use SORT without the USING clause to sort the color code table, Natural creates a "sort record" comprised of every variable in your program. Talk about overkill. Sorting all that additional data impacts performance.

The USING clause is highly recommended, as is Structured Mode.

[diptisaini]

The USING clause indicates the fields which are to be written to intermediate sort storage. What doesthis statement mean? Can any one explain me with an example ?

[dick scherrer]

Hello,

Do you understand what "intermediate storage" is?

Suggest you post your understanding of intermediate storage and someone can clarify if need be.

As far as an example, i believe you posted an example of some data that would use intermediate storage in the initial post.

One simple concept for intermediate storage is storage that is neither part of the input nor the output but is used rather "in the middle of the process".

[diptisaini]

SORT BY ACAS-CAMAST.ACTION-TYPE ACAS-CAMAST.OLD-ASSET-ID
USING ACAS-CAMAST.CA-NO ACAS-CAMAST.OLD-ASSET-DESC
ACAS-CAMAST.ISSUER-NAME #CAMAST-ISN


In above statement my doubt is as per above statement first it will sort record with Action-type then with old Asset-id then why we storing only ACAS-CAMAST.CA-NO, ACAS-CAMAST.OLD-ASSET-DESC ,ACAS-CAMAST.ISSUER-NAME, #CAMAST-ISN fileds into the intermediate storage by using USAGE CLAUSE. As in file we have more than 100 fileds then why we are not mentioning those fields into the USAGE CLAUSE.So, not able to undersatnd this concept.

[BillyBoyo]

Re-read RGZbrog's response.

[RGZbrog]

The following explanation presumes that the program you're looking at is a working, Structured Mode object.

Perhaps this exercise will explain things better.

Print the program and cut it into 4 sections. OK, you don't actually need to use scissors. Just draw a big red line or break it logically, in your mind:
1 below the END-DEFINE
2 between the END-ALL and SORT statements
3 below the SORT statement (including the USING clause)

The four sections are
S1 data
S2 input logic
S3 sort
S4 output logic

When you program is invoked, Natural knows that it contains a SORT statement, and allocates intermediate storage (that is, a block of memory) to hold a block of sort records. This storage is 'intermediate' in that it will be discarded at the end of your program.

S1 will contain a loop, and for each iteration, a sort record is created. (Execute an ESCAPE TOP to avoid this.)

If executed on-line, S2 invokes an in-memory sort. If executed in batch, S2 invokes the sort utility specified by the Natural Administrator, usually DFSORT or SYNCSORT.

S3 reads the sort output, just as a READ WORK would.

Now for the tricky bit.

If you DO NOT code the USING clause, how does Natural know what you need placed in the sort record? He doesn't, so the sort record is defined as the entire contents of S1. If you DO code the USING clause, only its field list is used in the sort record.

Not only does the USING clause improve performance, as I explained earlier in this thread, but it avoids logic error, too. For example, what if your S2 contained

Select all

READ view BY ACCOUNT
  ADD 1 TO #I

Let's say the first ACCOUNT, #11, is associated with surname ZBROG and the 100th ACCOUNT, #9, is associated with RALPH. The variable #I is used as a record count. Without the USING clause, the #I field is returned as part of the sort record. So, SORT BY SURNAME would return

Select all

Surname  ACCOUNT  #I
=======  =======  ===
RALPH    9        100
...
ZBROG    11       1

You can't use #I within S4, because its value is changed each time you read a sorted record. To avoid this logic error, you could remove the DEFINE DATA and use a RESET statement to create S1, as is typical in Reporting Mode. Then define another variable, say #J within S4. Yuch! In Structured mode, S1 is available to both S2 and S4, but only the USING list is available to S3.

Logically, S2, S3, and S4 are executed as separate modules, just like in a standard IBM batch sort, where input and output logic modules are defined.

As to your most recent question, Diptisaini, about the USING list containing only 3 of 100 fields in the view, it means your S4 logic doesn't need any more of those fields. And the programmer is wise ...