top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Solution to create empty data set file using Informatica Pre-SQL command when target is Mainframe file

+2 votes

Informatica Power Exchange connection will not allocate empty data set file if no data loaded to target.

Create empty data set file using Informatica Pre SQL command when Power Exchange file used as Target file.

Execute a CREATEFILE command to allocate the file in mainframe using "PreSQL" Command of Informatica.

Ensure to have following parameters to create an empty data set file in the mainframe.
* Data Set Name

Pre SQL Command Example:
SPACE= (TRK (10, 5))

Also ensure to have settings at the session level as shown below.
enter image description here

posted Apr 26, 2014 by Shatark Bajpai

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button
We need to generate empty data set in mainframe as a requirement. Currently datasets are getting generated in MF if there are 1 or more than 1 records. The problem is, no data set gets generated if there are no records for a particular day. As a solution, we tried to give pre-sql in session as you had mentioned above. But our session failed with the below error:
Message Code: PWXPC_12015
Message: [ERROR] Error message: [
[Informatica][SCLI PWX Driver] PWX-00265 DBAPI Error. DB_EXECSQL failed for file .
[Informatica][SCLI PWX Driver] PWX-01275 DBNTC ExecSQL failed for file , rcs 260/265/0.

Can you please provide us the exact pre-sql query and help us resolve this issue?

It is a bit urgent here!!  Many thanks is anticipation!!
This is a old thread, I would suggest that you ask a fresh question on and tag it as informatica people should be able to help you. But make sure you register first as registered user has higher chances of getting responses.
Have you mentioned the Data Set Name, RECFM, LRECL, SPACE details properly because its working fine at my end.

Related Articles

Most of the time when we process flat files in Informatica PowerCenter, we do some kind of file pre or post processing, such as unzip the source file, create a custom header or footer for the target file etc. Such processing is normally done using UNIX or Windows scripts, which is called using pre or post session script. Now Informatica PowerCenter has provided Source, Target Commands to make such processing easy than before.

File Command Property
Using Source or Target Command property, either a UNIX or a Windows command can be used to generate flat file source data input rows or file list or a session. Command writes data into stdout and PowerCenter interprets this as a file list or source data. We can use service process variables like $PMSourceFileDir in the command.

Use Cases for File Command Property
In this article, let’s discuss couple of use cases, which can be handled easily using File Source, Target Commands. These properties can be further used as per different business needs, but let’s see couple of them here.

Use Case 1: Read a Compressed Source File.
Before the file is read, the file needs to be unzipped. We do not need any other pre- session script to achieve this. This can be done easy with the below session setting.
enter image description here
This command configuration generates rows to stdout and the Flat file reader reads directly from stdout, hence removes need for staging data.

Use Case 2: Generating a File List.
For reading multiple file sources with same structure, we use indirect file method. Indirect file reading is made easy using File Command Property in the session configuration as shown below.
enter image description here
Command writes list of file names to stdout and PowerCenter interprets this as a file list.

Use Case 3: Zip the Target File.
We can zip the target file using a post session script. but this can be done without a post session script as shown in below session configuration.
enter image description here

Use Case 4: Custom Flat File Column Headings.
You can get the column heading for a flat file using the session configuration as below. This session setting will give a file with header record 'Cust ID, Name, Street #, City, State, ZIP'
enter image description here

Use Case 5: Custom Flat File Footer.
You can get the footer for a flat file using the session configuration as given in below image. This configuration will give you a file with ****** End Of The Report ****** as the last row of the file.
enter image description here

These properties can be further used as per different business needs.