output from hashed file

[ray.wurlod]

&HOLD& is a directory. However the "&" character is special to the shell, so needs to be quoted or escaped in operating system commands such as cd.

Code: Select all

cd \&HOLD\&

or

Code: Select all

cd '&HOLD&'

This is not a DataStage requirement - it is an operating system requirement.

A FMT specification in the query overrides the format specified in the file dictionary. Therefore, even though the file dictionary specified 40L as the format, the FMT '15L' in the query indicates that the display width shall be 15 characters left-justified, which is why Field4 wrapped in its (display)column.

[chulett]

a) But I am not able to cd (change directory) to &HOLD& or read the MyOutput file.

Why not? Is it because you are not escaping the "&" meta characters and so they send you into the background? Try:

Code: Select all

cd \&HOLD\&

as one way to get in there.

[mystuff]

Thanks, I am able to view the file.

However, does the SETPTR works only for select statements?
I tried the below

Code: Select all

SETPTR 4,1000,20000,0,0,3,BANNER listtest,BRIEF
LIST.DICT HashOther LPTR 4;

But I don't see the file getting created.

If I have

Code: Select all

SETPTR 4,1000,20000,0,0,3,BANNER hashedfile,BRIEF
SELECT * from HashOther LPTR 4;

I do see file getting created.

[ray.wurlod]

The file is overwritten if no new SETPTR command has been executed.

There are other options with BANNER if you need to generate file names (for example BANNER UNIQUE or BANNER NEXT), otherwise execute new SETPTR command ahead of each query.

[mystuff]

The file is overwritten if no new SETPTR command has been executed.

I have executed the setptr prior to the list.dict statement. There isnt any file.

[ray.wurlod]

LIST.DICT is not SQL so does not take a terminating ";" character.

[mystuff]

LIST.DICT is not SQL so does not take a terminating ";" character.

Even when I remove the ; , the same happens (i.e. the file doesn't get created in &HOLD& directory). Basically I want to obtain result from LIST.DICT and parse the column names.

[ray.wurlod]

OK, try it without the ".".

Code: Select all

LIST DICT H_DAY_ID LPTR 4

It works for me with or without the ".".

[mystuff]

a)

OK, try it without the ".".

Code: Select all

LIST DICT H_DAY_ID LPTR 4

It works for me with or without the ".".

I tried to the statement above and it worked i.e. able to write a file wihtin &HOLD&. But now I am back to my same question.. As the output from

Code: Select all

>SETPTR 4,1000,200000,0,0,3,BANNER HColOutput, BRIEF 
>LIST DICT H_DAY_ID LPTR 4 

Results in splitting of the column names, when they are more than 14 characters.

I want to obtain records without splitting (without using FMT 'L/R', as this would cause a restriction on generic code). the same thing for columns resulting from select statement irrespective of the specified FIELD.FMT in both cases i.e.

LIST DICT <HASH>
OR
SELECT * FROM <HASH>

THE COLUMNS SHOULD NOT GET SPLIT IRRESPECTIVE OF THEIR FORMAT (ie. 10L etc)

AND WITHOUT SPECIFYING 'FMT' IN STATEMENT AS THIS WOULD CAUSE A RESTRICTION ON THE CODE

[ray.wurlod]

There is no restricton - the query Engine is obeying the field widths specified in the metadata (file dictionary). This is How It Works.

If you want something different, YOU have to take control. Try this.

Code: Select all

SELECT EVAL "CONVERT(@FM:@VM,'~|',@RECORD)" FMT "132L#132" FROM hashedfile;

That form of FMT will truncate the line at 132 characters. It is not necessarily a solution, just a way to get you thinking about alternatives.

Note also that "*" does not necessarily mean "all columns" in DataStage/SQL.

[ray.wurlod]

Add SUPPRESS COLUMN HEADER to the query.

Code: Select all

SELECT EVAL "CONVERT(@FM:@VM,'~|',@RECORD)" FMT "132L#132" FROM hashedfile SUPPRESS COLUMN HEADER;

@RECORD is a system variable containing the entire data record (without the key column(s)). The CONVERT function converts field and multi-value delimiter characters into alternative printable characters.
The FMT specification defines that the output will be in a single column 132 characters wide, left justified, with a mask limiting the output to 132 characters maximum. Read about possibilities in the DataStage BASIC manual (the FMT() function).
If you want the key column(s) as well change the query to

Code: Select all

SELECT EVAL "CONVERT(@FM:@VM,'~|',@ID:@FM:@RECORD)" FMT "132L#132" FROM hashedfile SUPPRESS COLUMN HEADER;

[mystuff]

First of all I would like to thank for the patience shown in replying my questions. I went through some homework and have few questions/clarifications

a)

• I was trying to select just one field from DICT hashed file and used the query below. It works fine

SETPTR statement
SELECT FIELD.DEF FMT "132L#132" FROM DICT <hashedfile> SUPPRESS COLUMN HEADER LPTR 4;

• When I try to use convert for this, it fails

Code: Select all

SETPTR statement
SELECT EVAL "CONVERT(@VM:@FM,'~|',FIELD.DEF)" FMT "132L#132" FROM DICT <hashedfile> SUPPRESS COLUMN HEADER LPTR 4;

• the same convert statement works if i replace FIELD.DEF with FIELD (different column)

b)

The FMT specification defines that the output will be in a single column 132 characters wide, left justified, with a mask limiting the output to 132 characters maximum. Read about possibilities in the DataStage BASIC manual (the FMT() function).
If you want the key column(s) as well change the query to Code:

Code: Select all

SELECT EVAL "CONVERT(@FM:@VM,'~|',@ID:@FM:@RECORD)" FMT "132L#132" FROM hashedfile SUPPRESS COLUMN HEADER; 

• As this was masking the output to 132 I have used the below query. Output works good. But do shed your experience whether I am thinking the right way

Code: Select all

SETPTR statement 
SELECT EVAL "CONVERT(@FM:@VM:@TM,'|',@ID:@FM:@RECORD)" FMT "1000L" FROM hashedfile SUPPRESS COLUMN HEADER LPTR 4;

• Now the output works fine. But I want to confirm myself that it wasn't a fluke + have one question in it.

• i) Intention of above select query is to -- replace all @FM with '|' . Delete any @VM,@TM . New record will come in next line anyway because of Char(10) (so removing @TM will not matter).
  
  Am I thinking right???
  
  ii) FMT "1000L" allows to write to the file (via setptr) for 1000 characters. Not using mask in query, as I would need anything longer than 132 characters as well. It worked when I replaced "132L#132" with "1000L" and removed the mask.
  
  Am I thinking right???
  
  are the above mentioned two statements correct????
  
  A question on this query.
  
  iii) Since I am using 1000L, I am posing a limitation that a hashed file cannot contain a record more than 1000 characters. Is there any way to make this unlimited?

[ray.wurlod]

(a) If you're trying to display fields from the hashed file itself omit the word DICT from the query. In what sense do you mean "fail"?

(b) (i) Correct

(b) (ii) Incorrect. Without a mask data will "wrap around" in the column when a report is generated. The mask truncates the data, not the field width specified in the FMT.

(b) (iii) The FMT specification is only about the maximum column width when the data are displayed. It does not limit the size of data that can be stored (which is almost unlimited). The column width governs when (at what width) data "wrap around" in the column when a report is generated. There is no scope for making it unlimited (there's probably an upper limit of something like 65536). The second argument of SETPTR will need to be at least this large to make this approach sensible.

[mystuff]

a)

(a) If you're trying to display fields from the hashed file itself omit the word DICT from the query. In what sense do you mean "fail"?

• I am trying to read the column names from the hashed file so I need the DICT. And the reason I want to look for FIELD.DEF is because it has the column names in the order that it was created in hashed file.
  
  "fail" - It gives some compilation error. I will execute this tomorrow and get the exact info. Strange behaviour is that if I replaced FIELD.DEF with FIELD the query runs file.

b)

(b) (ii) Incorrect. Without a mask data will "wrap around" in the column when a report is generated. The mask truncates the data, not the field width specified in the FMT.

(b) (iii) The FMT specification is only about the maximum column width when the data are displayed. It does not limit the size of data that can be stored (which is almost unlimited). The column width governs when (at what width) data "wrap around" in the column when a report is generated. There is no scope for making it unlimited (there's probably an upper limit of something like 65536). The second argument of SETPTR will need to be at least this large to make this approach sensible.

• Oh so, when i used FMT 1000L - the data would have wrapped if the overall length was greater than 1000. So the safest way for me would be FMT "MaxValueL#MaxValue" also setting the same in second argument of SETPTR.

c)

The FMT specification is only about the maximum column width when the data are displayed. It does not limit the size of data that can be stored (which is almost unlimited).

• Considering that I want to capture the longest possible data stored (practically we avoid those situations). In such scenarios do we have to compromise by picking either Wrapping or Truncation or Both

.

[ray.wurlod]

(a) Sounds like FIELD.DEF is a compiled, read-only field, but which has not been compiled. Or it may not be a well-formed definition. Try executing this command

Code: Select all

CD DICT.DICT 

to see whether it improves matters.

(b) Correct. But the arguments to SETPTR are purely numeric.

(c) Yes, you have to have a mask (Truncation) or not have a mask (Wrapping). Your choice.