{"id":1859,"date":"2019-07-17T08:54:56","date_gmt":"2019-07-17T00:54:56","guid":{"rendered":"http:\/\/www.hefeiyu.com\/?p=1859"},"modified":"2019-07-17T08:55:52","modified_gmt":"2019-07-17T00:55:52","slug":"tiptop%e5%ae%9a%e6%9c%9f%e6%b8%85%e9%99%a4cr_ds_report%e8%b5%84%e6%96%99%e5%ba%93%e4%b8%ad%e7%9a%84%e6%9a%82%e5%ad%98%e8%a1%a8","status":"publish","type":"post","link":"https:\/\/www.hefeiyu.com\/?p=1859","title":{"rendered":"TIPTOP\u5b9a\u671f\u6e05\u9664cr_ds_report\u8d44\u6599\u5e93\u4e2d\u7684\u6682\u5b58\u8868"},"content":{"rendered":"<p>1\u5b89\u88dd\u8a2d\u5b9a<\/p>\n<p>2\u55ae\u7368\u57f7\u884c<\/p>\n<p>3\u8a2d\u5b9a\u6392\u7a0b\uff0c\u5b9a\u6642\u57f7\u884c<\/p>\n<p>4\u4f7f\u7528\u8cc7\u6599\u5eab\u5de5\u5177\u6aa2\u67e5\u66ab\u5b58\u8868\u662f\u5426\u5df2\u88ab\u522a\u9664<\/p>\n<p>&nbsp;<\/p>\n<p>1\u5b89\u88c5\u8bbe\u5b9a<\/p>\n<p>\u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP GP\u4e3b\u673a<\/p>\n<p>\u5c06drop_ds_report_temptable.4gl\u590d\u5236\u5230$DS4GL\/bin\u76ee\u5f55\u4e0b<\/p>\n<p>\u5207\u5230$DS4GL\/bin\u76ee\u5f55\u4e0b\uff0c\u7f16\u8bd1\u6863\u6848<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.hefeiyu.com\/wp-content\/uploads\/2019\/07\/7dded954eb856d94d045a917afb5e8b1.png\" \/><\/p>\n<p>l\u8fde\u7ed3\u6863\u6848\uff0c\u4ea7\u751fdrop_ds_report_temptable.42r\u6863<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.hefeiyu.com\/wp-content\/uploads\/2019\/07\/03e0a124a636b580c65b8320a22b35bc.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>2\u5355\u72ec\u6267\u884c<\/p>\n<p>\u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP GP\u4e3b\u673a<\/p>\n<p>cd $DS4GL\/bin<\/p>\n<p>$FGLRUN drop_ds_report_temptable 1<\/p>\n<p>\u53c2\u6570\u8bf4\u660e: 1\u4ee3\u88681\u5929\u524d\u6240\u4ea7\u751f\u7684Temp Table<\/p>\n<p>\u82e5\u4eca\u5929\u4e3a 2008\/9\/19, \u5219\u6b64\u6307\u4ee4\u4f1a\u5c06 2008\/9\/17\u4ee5\u524d\u6240\u4ea7\u751f\u7684Temp Table\u5220\u9664, \u53ea\u7559\u4e0b\u00a0 2008\/9\/18\u4ee5\u540e\u4ea7\u751f\u7684Temp Table\u3002\u82e5\u4e0d\u8f93\u5165\u53c2\u6570\uff0c\u5219\u9884\u8bbe\u4f7f\u75281\u5929\u524d\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>3\u8bbe\u5b9a\u6392\u7a0b\uff0c\u5b9a\u65f6\u6267\u884c<\/p>\n<p>\u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP\u4e3b\u673a<\/p>\n<p>\u8f93\u5165crontab \u2013e \u5bf9crontab\u8fdb\u884c\u7f16\u8f91<\/p>\n<p>\u52a0\u5165<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.hefeiyu.com\/wp-content\/uploads\/2019\/07\/cb06dbdf3b43004aaf9eccde999c5283.png\" \/><\/p>\n<p>\u6309[ESC]\u540e\uff0c\u8f93\u5165 :wq \u5b58\u6863\u540e\u79bb\u5f00\u3002<\/p>\n<p>\u8bf4\u660e: \/u2\/topprod\u4e3aTIPTOP\u6b63\u5f0f\u533a\u76ee\u5f55\uff1b\/u2\/toptest\u4e3aTIPTOP\u6d4b\u8bd5\u533a\u76ee\u5f55\uff1b\/u2\/topstd\u4e3aTIPTOP\u6807\u51c6\u533a\u76ee\u5f55\uff0c\u8fd9\u4e09\u4e2a\u76ee\u5f55\u9700\u89c6\u5b9e\u9645\u5b89\u88c5\u60c5\u51b5\u800c\u5fc5\u987b\u52a0\u4ee5\u4fee\u6539\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>4\u68c0\u67e5\u6682\u5b58\u8868\u662f\u5426\u5df2\u88ab\u5220\u9664<\/p>\n<p>Oracle\uff1a<\/p>\n<p>\u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP\u4e3b\u673a<\/p>\n<p>\u6267\u884c sqlplus ds_report@$ORACLE_SID<\/p>\n<p>\u5728sqlplus\u4e2d\u4e0b<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.hefeiyu.com\/wp-content\/uploads\/2019\/07\/824675e9c4760e0a20bd7a6aace06d47.png\" \/><\/p>\n<p>\u82e5\u7ed3\u679c\u4e3a0 \u4ee3\u8868\u4e00\u5929\u524d\u7684\u6682\u5b58\u8868\u5df2\u7ecf\u5168\u90e8\u88ab\u5220\u9664<\/p>\n<p>Informix\uff1a<\/p>\n<p>\u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP\u4e3b\u673a<\/p>\n<p>\u6267\u884c dbaccess ds_report<\/p>\n<p>\u5728dbaccess\u4e2d\u7684Query Language\u4e2d\u4e0b<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/www.hefeiyu.com\/wp-content\/uploads\/2019\/07\/dca8c5ccec2a7165f2f253ec8ff2c5a6.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>\u82e5\u7ed3\u679c\u4e3a0 \u4ee3\u8868\u4e00\u5929\u524d\u7684\u6682\u5b58\u8868\u5df2\u7ecf\u5168\u90e8\u88ab\u5220\u9664<\/p>\n<p>&nbsp;<\/p>\n<p>\u9644\u00a0drop_ds_report_temptable.4gl \u4ee3\u7801\uff1a<\/p>\n<pre class=\"pure-highlightjs\"><code class=\"null\">##################################################\r\n# Description  \t: Drop ds_report Temp tables (1 day ago)\r\n# Date &amp; Author : 2008\/09\/11 by jacklai\r\n# Parameter   \t: l_interval1 (days)\r\n# Return   \t    : none\r\n# Usage        \t: $FGLRUN drop_ds_report_temptable 1\r\n# Modify        : No:FUN-880111 08\/09\/11 By jacklai Drop ds_report Temp tables (1 day ago)\r\n# Modify        : No:TQC-890062 08\/09\/30 By jacklai \ue150\u30bf\ue6c8Oracle 10g \ue5fd\u7a66\ue6c3\ue0cb\u79e8\u5e01recycle bin\u8bc0\ue7d1\u03c4\u65e7\u7493\u7958\u0391\u78c5\ufe3d\u3044\u801e\r\n##################################################\r\n\r\nDATABASE ds\r\n\r\nMAIN\r\n  DEFINE l_dbtype     STRING\r\n  DEFINE l_sql        STRING\r\n  DEFINE l_dropsql    STRING\r\n  DEFINE l_rec        RECORD\r\n         l_tablename  VARCHAR(128),\r\n         l_created    DATE\r\n  END RECORD\r\n  DEFINE l_interval1  INTERVAL DAY(5) TO DAY\r\n  DEFINE l_interval2  INTERVAL DAY(5) TO DAY\r\n  DEFINE l_version    VARCHAR(128)          #No:TQC-890062\r\n  DEFINE l_tok        base.StringTokenizer  #No:TQC-890062\r\n  DEFINE l_vermaj     INTEGER               #No:TQC-890062\r\n  DEFINE l_RecycleOff STRING                #No:TQC-890062\r\n  \r\n  WHENEVER ERROR CONTINUE #No:TQC-890062\r\n\r\n  CLOSE DATABASE\r\n  DATABASE ds_report\r\n  \r\n  #command line argument[1]: days (default: 1)\r\n  IF NUM_ARGS() &gt;= 1 THEN\r\n    LET l_interval1 = ARG_VAL(1)\r\n  ELSE\r\n    LET l_interval1 = \"1\"\r\n  END IF\r\n  \r\n  #get information of tables from database \r\n  LET l_dbtype = DB_GET_DATABASE_TYPE()\r\n  CASE l_dbtype\r\n  WHEN \"ORA\"\r\n    #No:TQC-890062 --start--\r\n    #Get Oracle version\r\n    PREPARE pre0 FROM \"SELECT VERSION FROM PRODUCT_COMPONENT_VERSION WHERE PRODUCT LIKE 'Oracle%'\"\r\n    DECLARE cur0 CURSOR FOR pre0\r\n    OPEN cur0\r\n    FETCH cur0 INTO l_version\r\n    CLOSE cur0\r\n    LET l_tok = base.StringTokenizer.create(l_version, \".\")\r\n    #get major version number\r\n    IF l_tok.hasMoreTokens() THEN\r\n      LET l_vermaj = l_tok.nextToken()\r\n    END IF\r\n    #No:TQC-890062 --end--\r\n    LET l_sql = \"SELECT OBJECT_NAME, CREATED FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' ORDER BY OBJECT_NAME\"\r\n  WHEN \"IFX\"\r\n    LET l_sql = \"SELECT TABNAME, CREATED FROM SYSTABLES WHERE TABID &gt;= 100 AND TABTYPE='T' ORDER BY TABNAME\"\r\n  END CASE\r\n  \r\n  #No:TQC-890062 --start--\r\n  #\ue6c8oracle 10g\ue64e\ue5fd\ue008\u30bb\ue6c8\ue718\u57c3Table\u73a1\u74b6\u95fd\u8d85RecycleBin\ue662\ue215\r\n  IF l_dbtype = \"ORA\" AND l_vermaj &gt;= 10 THEN\r\n    EXECUTE IMMEDIATE \"ALTER SESSION SET RECYCLEBIN=OFF\"\r\n  END IF\r\n  #No:TQC-890062 --end--\r\n  \r\n  PREPARE pre1 FROM l_sql\r\n  DECLARE cur1 CURSOR FOR pre1\r\n  FOREACH cur1 INTO l_rec.*\r\n    IF STATUS == NOTFOUND THEN\r\n      EXIT FOREACH\r\n    END IF\r\n    LET l_interval2 =  CURRENT YEAR TO DAY - l_rec.l_created\r\n    IF l_interval2 &gt; l_interval1 THEN\r\n      LET l_dropsql = \"DROP TABLE \",l_rec.l_tablename\r\n      EXECUTE IMMEDIATE l_dropsql\r\n      IF SQLCA.SQLCODE THEN\r\n        ERROR l_dropsql,\" ERROE: \",SQLCA.SQLCODE\r\n      ELSE\r\n        DISPLAY l_dropsql,\" OK.\"\r\n      END IF\r\n    END IF\r\n  END FOREACH\r\n  \r\n  CLOSE DATABASE\r\nEND MAIN\r\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<p>\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a<a href=\"https:\/\/www.hefeiyu.com\">\u8d6b\u975e\u57df<\/a> &raquo; <a href=\"https:\/\/www.hefeiyu.com\/?p=1859\">TIPTOP\u5b9a\u671f\u6e05\u9664cr_ds_report\u8d44\u6599\u5e93\u4e2d\u7684\u6682\u5b58\u8868<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>1\u5b89\u88dd\u8a2d\u5b9a 2\u55ae\u7368\u57f7\u884c 3\u8a2d\u5b9a\u6392\u7a0b\uff0c\u5b9a\u6642\u57f7\u884c 4\u4f7f\u7528\u8cc7\u6599\u5eab\u5de5\u5177\u6aa2\u67e5\u66ab\u5b58\u8868\u662f\u5426\u5df2\u88ab\u522a\u9664 &nbsp; 1\u5b89\u88c5\u8bbe\u5b9a \u4ee5tiptop\u8d26\u53f7\u767b\u5165TIPTOP GP\u4e3b\u673a \u5c06drop_ds_report_temptable.4gl\u590d\u5236\u5230$DS4GL\/bin\u76ee\u5f55\u4e0b  [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,26],"tags":[439,613,10],"class_list":["post-1859","post","type-post","status-publish","format-standard","hentry","category-unix","category-4gl","tag-ds","tag-report","tag-tiptop"],"_links":{"self":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1859","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1859"}],"version-history":[{"count":2,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1859\/revisions"}],"predecessor-version":[{"id":1861,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1859\/revisions\/1861"}],"wp:attachment":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1859"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1859"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1859"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}