File url with timestamp for delimited_data_writer

tava.amitava

• August 07, 2009 00:00

• Answered

Follow

Hi,
I have created a graph to create a delimited text file by reading data from database. Whenever I run the graph it writes the result in the same file. I want to give the file name with time stamp so that every time a separate file generate.

<Phase number="0">
	<Node dbConnection="conn" id="INPUT0" sqlQuery="select 'HEADER','SAO','SAO AIP',rtrim(CHAR(year(current timestamp)))||RIGHT('0'||RTRIM(CHAR(month(current timestamp))),2)||RIGHT('0'||rtrim(CHAR(day(current timestamp))),2)||RIGHT('0'||rtrim(CHAR(HOUR (current timestamp))),2)||RIGHT('0'||rtrim(CHAR(MINUTE (current timestamp))),2),'06','00' from sysibm.sysdummy1" type="DB_INPUT_TABLE"/>
	<Node append="false" fileURL="../files/download/SAO_FILE_SMSMESSAGE.dat" id="OUTPUT0" type="DELIMITED_DATA_WRITER"/>				 
	<Edge id="INEDGE0" fromNode="INPUT0:0" toNode="OUTPUT0:0" metadata="headerMeta"/>
</Phase>

Thanks for your help.
Regards,
Amit

• 

  avackova

  • August 07, 2009 09:13

  0

  Comment actions Permalink

  Hello Amit,
  you can use auto filling field in your reader with row_timestamp or reader_timestamp function (see auto_filling), and then partitionKey in your writer (see Data Writer).
  See following graph as an example:

  <?xml version="1.0" encoding="UTF-8"?>
  <Graph name="Testing DB Unload" >
  <Global>
  <Metadata id="Metadata0" >
  <Record fieldDelimiter=";" name="employee" recordDelimiter="\n" recordSize="-1" type="delimited">
  <Field name="employee_id" nullable="true" shift="0" type="integer"/>
  <Field name="full_name" nullable="true" shift="0" type="string"/>
  <Field auto_filling="reader_timestamp" format="HHmmss" name="time" type="date"/>
  </Record>
  </Metadata>
  <Metadata id="Metadata1" >
  <Record fieldDelimiter=";" name="employee" recordDelimiter="\n" recordSize="-1" type="delimited">
  <Field name="employee_id" nullable="true" shift="0" type="integer"/>
  <Field name="full_name" nullable="true" shift="0" type="string"/>
  <Field name="time" type="string"/>
  </Record>
  </Metadata>
  <Connection dbConfig="${CONN_DIR}/postgre.cfg" id="Connection0" type="JDBC"/>
  <Property fileURL="workspace.prm" id="GraphParameter0"/>
  </Global>
  <Phase number="1">
  <Node dbConnection="Connection0"  id="INPUT" sqlQuery="select * from employee" type="DB_INPUT_TABLE"/>
  <Node append="false" excludeFields="time" fileURL="${DATAOUT_DIR}/employees." id="OUTPUT" partitionFileTag="keyNameFileTag" partitionKey="time" type="DATA_WRITER"/>
  <Node id="REFORMAT0" type="REFORMAT">
  <attr name="transform"><![CDATA[//#TL
  
  // Transforms input record into output record.
  function transform() {
  	$0.employee_id := $0.employee_id;
  	$0.full_name := $0.full_name;
  	$0.time := date2str($0.time, "HH-mm-ss") + ".dat";
  }
  
  ]]></attr>
  </Node>
  <Edge debugMode="true" fromNode="INPUT:0"  id="INEDGE" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="REFORMAT0:0"/>
  <Edge fromNode="REFORMAT0:0" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="OUTPUT:0"/>
  </Phase>
  </Graph>
  

• 

  mzatopek

  • September 14, 2009 11:30

  0

  Comment actions Permalink

  Probably the most easiest way how to reach your goal is to exploit the new functionality, which is available since 2.8 version. Consider the CTL statement in an arbitrary component attribute:
  
  fileURL="${DATAOUT_DIR}/result`date2str(today(), "ddMMyyyy")`.txt"
  
  This is probably exactly what you need ;)

Please sign in to leave a comment.