Skip to Main Content
Status Released
Workspace CONNX
Created by Stephane Moreau
Created on Jul 27, 2021

unsetfilename keyword

About the “fn setfilename», it overrides the logicalname usual target by the one set in the SQL query

Meaning that if by default the KSCL_COCUST logical name target this file :


By doing the here below setfilename

select * from jupiter.dbo.co_customers where cust_name='MLE BUCAS Karenn' {fn setfilename CO_CUSTOMERS,'WORK:[jupiter.user.STM.REFRESH]TBL_CO_CUSTOMERS.DAT;2'} {statistics};

it now targeting the he below file :


Unfortunately, as we are using a pool of connections (with a pool configuration which does not close physically the connection, but remain them available for a new program when the connection are “free”) , the RMS targeted file remain the same as the one declared by the latest setfilename. Which is not good for the next program which need to target the original table file.

Even if a flushopenfilecache is launched at the end of a job (in order to close the RMS opendfile of this specific connection), this has no impact as you said on the setfilename declaration.

Is there a way to have an UNsetfilename feature in order to go back to the original filename (the one known by the CDD) ?

In this example, an unsetfilename keyword would define again the here below target for the co_custumer table.


In that case when a job is finished, and before give the connection back to the pool, we should ran flushopenfilecache + unsetfilename keywords

I guess a keyword like refreshCDD or something like that could help us (but I think refreshCDD makes too many unwanted things on this case)

Fore sure we can also do the here below query to set it back to the original target

select * from jupiter.dbo.co_customers where cust_name='MLE BUCAS Karenn' {fn setfilename CO_CUSTOMERS,'DATA:[DISK_CORE4.CORE.TABLE_DATA]PRODUCT.TBL_CO_CUSTOMERS',refresh} {statistics}

but doing like that :

1/ it do a select when we don’t want to do it (so an useless load)

2/ it requires to know what was the previous targeted RMS files (PATH+filename), which requires to implement a list of all the “logical+table+path+file” on linux side

Have you an idea on your side the best way to proceed in order to go back to the original RMS file ?

Use Case be capable to go back to original target after using a setfilename keyword.
Created on Brainstorm 05/05/2020 7:37
Brainstorm ID 7952