Can Server Routines take arrays as Input Parameters?

vivek_rs · Post by **vivek_rs** » Wed Dec 01, 2004 4:38 am

Hi
Can Server Routines take arrays as Input Parameters?
If Yes, How Do I Do It?

xcb · Post by **xcb** » Wed Dec 01, 2004 5:37 am

You can pass a dynamic array as a parameter to a routine, as a dynamic array is in essence only a delimited string. I've never tried it with a dimensioned array but I don't think that it would be possible, certainly not through a transformer anyway.

A dynamic array is created by appending system delimiters to a string.

eg. str1 = 'value1':@FM:'value2'

will produce str1 as a dynamic array with 2 elements. You can access them by using <> syntax. eg. str1<1> will return 'value1'. This can be created in stage variables or transformer derivations.

You can learn more about dynamic arrays by reading the Basic guide that shipped with datastage, there is a section dedicated to arrays.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 01, 2004 2:23 pm

The technical answer to the original question is yes. The architecture permits routine arguments to be (dimensioned) arrays. And how this can be accomplished is to be found in the DataStage BASIC manual.

However, neither the editing controls for routines nor the calling mechanisms within DataStage allow you to use this functionality.

Dynamic arrays, as Cameron pointed out, can be used, since these are simply delimited strings. I would suggest @FM rather than @VM as a delimiter character because, when @FM is used, there are potential performance benefits to be had due to an architectural feature called a "hint structure" that is only available in field-mark-delimited dynamic arrays. The angle-bracket notation with a single number implies a field-mark-delimited dynamic array.

Therefore, the answer to the second question posed in the original post is "you can't".

Was this an academic question or is there something specific that you wanted to do?

xcb · Post by **xcb** » Wed Dec 01, 2004 4:45 pm

Ray,

Did you mean @VM rather than @FM?

ray.wurlod · Post by **ray.wurlod** » Wed Dec 01, 2004 7:25 pm

No. I meant exactly what I typed. @FM is to be preferred wherever possible. Particularly if you want to access elements somewhat randomly, because the hint structure is of most value in this case.

The only time I would use @VM is where data are returned from a multi-valued field and I wanted to decompose it. And even them I might contemplate using RAISE or CONVERT to prefer @FM to @VM.