{"id":559,"date":"2014-05-22T10:47:03","date_gmt":"2014-05-22T10:47:03","guid":{"rendered":"http:\/\/tomvanmaanen.nl\/?p=559"},"modified":"2014-05-22T10:47:03","modified_gmt":"2014-05-22T10:47:03","slug":"running-scripts-in-teradata","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=559","title":{"rendered":"Running scripts in teradata"},"content":{"rendered":"<p>Let us assume, we have a data warehouse in teradata. Let us suppose that this data warehouse is loaded with a set of scripts. In principle it could be done. The question then arises how the scripts are run. An example could clarify this. The example looks like:<\/p>\n<pre>\n.logon oTDD001.s2.ms.****.com\/TOM.VAN-MAANEN, pau26688\n.export report file=C:\\Users\\TOM.VAN-MAANEN\\phi.txt\n.set retlimit 20\nSELECT\t'\"'||trim(Ident)||'\";\"'|| trim(Serial)||'\";\"'|| trim(Node)||'\"' FROM\tSAN_D_FAAPOC_01.TestUnicode;\n.export reset;\n.quit\n<\/pre>\n<p>The first line of this script creates a connection to a Teradata machine. The second line allocates a file that will be used to write data to. The fourth line is standard example where records from a table are exported. The last lines finishes the export and finishes the export from a table.<br \/>\nThis scripts is called by a piped command where the script is sent to the bteq: <\/p>\n<pre>\nC:\\Users\\TOM.VAN-MAANEN>bteq < bteq.txt\nBTEQ 13.10.00.01 Thu May 22 11:28:08 2014\n\n+---------+---------+---------+---------+---------+---------+---------+----\n.logon oTDD001.s2.ms.unilever.com\/TOM.VAN-MAANEN,\n\n *** Logon successfully completed.\n *** Teradata Database Release is 14.00.06.05\n *** Teradata Database Version is 14.00.06.05\n *** Transaction Semantics are BTET.\n *** Session Character Set Name is 'ASCII'.\n\n *** Total elapsed time was 1 second.\n\n+---------+---------+---------+---------+---------+---------+---------+----\n.export report file=C:\\Users\\TOM.VAN-MAANEN\\phi.txt\n *** To reset export, type .EXPORT RESET\n+---------+---------+---------+---------+---------+---------+---------+----\n.set retlimit 20\n+---------+---------+---------+---------+---------+---------+---------+----\ninsert into SAN_D_FAAPOC_01.TestUnicode(ident,node,serial)\n        values('Test\u251c\u2563\u2500\u00d6Summary','Test\u251c\u2563\u2500\u00d6Summary',' 19');\n\n *** Insert completed. One row added.\n *** Total elapsed time was 1 second.\n\n\n+---------+---------+---------+---------+---------+---------+---------+----\nSELECT '\"'||trim(Ident)||'\";\"'|| trim(Serial)||'\";\"'|| trim(Node)||'\"'\nFROM SAN_D_FAAPOC_01.TestUnicode;\n\n *** Query completed. 16 rows found. One column returned.\n *** Total elapsed time was 1 second.\n\n\n+---------+---------+---------+---------+---------+---------+---------+----\n.export reset;\n *** Output returned to console.\n+---------+---------+---------+---------+---------+---------+---------+----\n.quit\n *** You are now logged off from the DBC.\n *** Exiting BTEQ...\n *** RC (return code) = 0\n\nC:\\Users\\TOM.VAN-MAANEN>\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Let us assume, we have a data warehouse in teradata. Let us suppose that this data warehouse is loaded with a set of scripts. In principle it could be done. The question then arises how the scripts are run. An example could clarify this. The example looks like: .logon oTDD001.s2.ms.****.com\/TOM.VAN-MAANEN, pau26688 .export report file=C:\\Users\\TOM.VAN-MAANEN\\phi.txt .set [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-559","post","type-post","status-publish","format-standard","hentry","category-data-warehousing"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/559","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=559"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/559\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=559"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=559"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=559"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}