Ricky

链接

RSS

RSS Link

去掉烦人的内存或磁盘空间不足

2015年11月23日 16:56 | Comments(0) | Category:Other | Tags:

file-block-7

 

打开网络上的文件或者邮件附件老是报错,是由于NTFS文件系统的安全策略导致。

用Perl查找包含非中文字符的记录

2015年7月28日 09:04 | Comments(0) | Category:Other | Tags:

#!/usr/bin/perl
#Author: 
#Version: 0.1
#Description: find all non chs chars
#Usage: 

use Getopt::Std;

Getopt::Std::getopts('f:', \%options);
$cifaFile=$options{f};
$nonChsFile="result.$cifaFile";

no warnings qw(uninitialized);
use open ":encoding(utf8)",":std";

open(cifa,"$cifaFile") || die "can't open";
open(nonChs,">$nonChsFile") || die "can't open";

while(<cifa>){
    $line=$_;
    #split with seperator
    @array = split(/\|ine,3);
    if ($array[1] ne "0" and $array[2]!~/^[\x{4E00}-\x{9FA5}]+$/){
        #write search result if found
        print nonChs $line;
    }
}

close(cifa);
close(nonChs);

选择一天中最大记录

2015年7月02日 11:33 | Comments(0) | Category:Other | Tags:

select s1.* 
from sensorTable s1
inner join 
(
  SELECT sensorID, max(timestamp) as mts
  FROM sensorTable 
  GROUP BY sensorID 
) s2 on s2.sensorID = s1.sensorID and s1.timestamp = s2.mts

Excel VBA宏中使用正则表达式

2015年6月18日 10:02 | Comments(0) | Category:Other | Tags:

 
'定义变量类型
Dim reg As Object
Set reg = CreateObject("vbscript.RegExp")

'定义正则表达式
With reg
    .Global = True
    .Pattern = "^[0-9]{4}$"
End With
'测试是否符合表达式
If Not reg.test(Range("$A$" & i).Value) Then
    Range("$A$" & i).Select
    errMsg = 。。。
    Application.Run "'" & ActiveWorkbook.Name & "'!ExitSub"
End If
 
 
除了test还有其他函数可以调用,比如replace等。

 

DB2使用正则表达式

2015年5月25日 15:13 | Comments(0) | Category:Other | Tags:

DB2 for z/OS需要用PASSING AS语句
 
SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, ''[^0-9]+$'')' PASSING column AS column) AS integer)  = 1
 
DB2 for LUW则不需要
 
SELECT * from table  WHERE 
xmlcast(xmlquery('fn:matches($column, ''[^0-9]+$'')') AS integer)  = 1

 

Everything中调用Total Commander打开文件夹

2015年5月15日 16:03 | Comments(0) | Category:Other | Tags:

explore_folder_command=$exec("D:\Program Files\Total Commander\TOTALCMD64.EXE" "$parent(%1)")
explore_folder_path_command=$exec("D:\Program Files\Total Commander\TOTALCMD64.EXE" "$parent(%1)")
open_folder_command=$exec("D:\Program Files\Total Commander\TOTALCMD64.EXE" "$parent(%1)")
open_folder_path_command=$exec("D:\Program Files\Total Commander\TOTALCMD64.EXE" "$parent(%1)")

批量自动导入VCF格式名片文件到Outlook通讯录

2015年5月15日 15:34 | Comments(0) | Category:Other | Tags:

Sub massImport()

    ' Initialise Variables
    Dim objWSHShell
    Dim objOutlook 
    Dim objActiveInspector
    Dim strVCFilename 
    Dim objFileSystemObject 
    Dim objFSOFile 
    Dim objItem
    Dim lngReturnValue 
    Dim olDiscard
    Dim cntImported As Integer

    'change current working directory
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    sWorkingDirectory = ThisWorkbook.Path & "\"
    impWorkBookName = ThisWorkbook.Name
    Application.Visible = False

    'create object
    Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")       
    Set objWSHShell = CreateObject("WScript.Shell")       

    vcfFile = Dir(sWorkingDirectory & "*.vcf")
    Do While vcfFile <> ""

        'get full name with path
        strVCFilename = sWorkingDirectory & vcfFile

        'connect outlook
        Set objOutlook = CreateObject("Outlook.Application")
        If Not(objOutlook Is Nothing) Then 
            'handle file name with space
            lngReturnValue = objWSHShell.Run (Chr(34) & strVCFilename & Chr(34), 0, True)

            Set objActiveInspector = objOutlook.ActiveInspector
            Set objItem = objActiveInspector.CurrentItem

            'save and close if outlook contact card object
            If (objItem.Class = olContact) Then
                objActiveInspector.CurrentItem.Save
                objActiveInspector.CurrentItem.Close olDiscard
                cntImported = cntImported + 1
            End If

            'clear
            Set objItem  = Nothing
            Set objActiveInspector  = Nothing
            Set objOutlook = Nothing
        Else
            MsgBox "Outlook连接错误," & strVCFilename & "不能导入"
        End If
        vcfFile = Dir
    Loop

    'clear
    Set objFileSystemObject = Nothing     
    Set objWSHShell = Nothing
    Application.Visible = True
    Workbooks(impWorkBookName).Activate

    MsgBox "共导入联系人数:" & cntImported

End Sub

自动在当前目录搜索vcf文件并批量导入,不需要在VBA工程中引用Scripting Host什么的,比较方便

Windows的ftp批处理脚本上传文件到z/OS

2015年3月05日 17:23 | Comments(0) | Category:Other | Tags:

写过多次了,忘了之前可以通过命令调用的版本了

支持中文转换及大文件(需要调整pri sec参数),自动去掉文件后缀

Vim使用微软雅黑字体

2014年9月09日 13:16 | Comments(0) | Category:Other | Tags:

加上编译选项

CFLAGS = $(CFLAGS) -DFEAT_PROPORTIONAL_FONTS

DB2密码过期修改密码

2014年5月09日 13:14 | Comments(0) | Category:Other | Tags:

    * Change by connecting to database

C:\> db2 connect to SAMPLE user TEST using OLDPWD new NEWPWD confirm NEWPWD
C:\> db2 connect to SAMPLE user TEST using OLDPWD change password

    * Change by attaching to node, this way works out for the limitation in Configuration Assistance

C:\> db2 attach to NODE user TEST using OLDPWD new NEWPWD confirm NEWPWD
C:\> db2 attach to NODE user TEST using OLDPWD change password

DB2中的ROWID和游标

2014年5月09日 10:18 | Comments(0) | Category:Other | Tags:

DB2的RID对于分区表同样是唯一值:

Declare c1 cursor with hold for
Select
Pk,
Value, RID(t)
Into
:Va,
:Vb
 
from tbname t
Order by Pk
For fetch only
 
fetch pk,value, my-rid
 
Update:
Set
Value = :Vb
Where
RID(t) = my-rid
If the row is not the same row as fetched it will give you a +100
 

DB2中获取多行游标的使用

2014年5月04日 14:45 | Comments(0) | Category:Other | Tags:

声明:

DECLARE C_TAB_R CURSOR WITH ROWSET POSITIONING FOR
                   SELECT ...
                   WHERE ...
 
FETCH:
               FETCH NEXT ROWSET
                   FROM C_TAB_R FOR 2 ROWS
               INTO :WK-TAB-DESC
               INDICATOR :WK-ROWS-INDS
 
WK-TAB-DESC 是一个容纳两条记录的数组,WK-ROWS-INDS是一个压缩整形PIC S9(4) COMP-4 OCCURS 2 TIMES

SQL计算字段中字符数量

2014年4月25日 17:17 | Comments(0) | Category:Other | Tags:

计算#个数

select (length(CERT_GR_STUS) - length(replace(CERT_GR_STUS,'#'))) / length('1') from ...

Rexx调用Unix命令展开JCL

2013年12月20日 13:34 | Comments(0) | Category:Other | Tags:

Rexx calls OMVS/USS command to expand JCL with parms

 

/** rexx vim: set ft=rexx: **/
/*author: Ricky Wu*/
/*function: expand JCL*/

arg jclsrc
jclsrc = strip(jclsrc,,"'")
isredit macro
uid=SYSVAR(SYSUID)

/*check parm*/
if length(jclsrc) = 0 then
do
    address isredit "(dsname) = dataset"
    address isredit "(mbr)    = member"
    jclsrc=dsname || "(" || mbr || ")"
end

/*check Unix System Services*/
if syscalls("ON") <> 0 then
    do
        say "Unix System Services not unavailable"
        exit
    end

address tso "oput "jclsrc" '/tmp/"uid"J'"

"ALLOC F(XXIN) DS('"jclsrc"') SHR REUSE"
"EXECIO * DISKR XXIN (FINIS STEM in."
"FREE F(XXIN)"
/* search from first line to end of file */
do recid = 4 to 6
    startcol = 0
    endcol   = 0
    startcol = pos("=",in.recid,20)
    if startcol <> 0 then
        endcol = pos(" ",in.recid,startcol)
    /*parse lib and member*/
    if startcol <> 0 & endcol <> 0 then 
        do
            if recid = 4 then
                do
                    len = endcol - startcol - 1
                    libFolder = substr(in.recid, startcol + 1, len)
                    /*check for multi lib*/
                    mulCol = pos('(', in.recid, startcol)
                    comCol = pos(',', in.recid, startcol)
                    if mulCol <> 0 then
                        do
                            len = comCol - mulCol - 1
                            libFolder = substr(in.recid, mulCol + 1, len)
                        end
                end
            else
            do
                /*get member name*/
                len = endcol - startcol - 1
                memName = substr(in.recid, startcol + 1, len)
                cmd = 'cat "//'''libFolder"("memName")'"" "
                cmd = cmd || ">> /tmp/ricky"
                call bpxwunix cmd
            end
        end
    else
        do
            if recid = 4 then
                say "Error: = sign not found"
        end
end

/*parse env var.*/
cmd = 'sed -e "\/\/\*/d;s/\/\/ *SET /\&/g;s/=''/\.=/g;s/=\&/\.=\&/g;s/'''
cmd = cmd || ' *$//g;s/ *$//g;" /tmp/ricky' 
cmd = cmd || " > /tmp/parm;"
cmd = cmd || "awk -F= 'BEGIN{ while((getline<""/tmp/parm"")>0)"
cmd = cmd || "{ if ($0~/^\&ENV\./){a[$1]=($2);} }"
cmd = cmd || " close(""/tmp/parm"") } { for(x in a)"
cmd = cmd || "{ if ($0~x){ gsub(x, a[x]); } }"
cmd = cmd || " print$0 }' /tmp/parm > /tmp/env;"
cmd = cmd || "awk -F= 'BEGIN{ while((getline<""/tmp/env"")>0)"
cmd = cmd || "{ if ($0~/^\&.*\./){a[$1]=($2);} }"
cmd = cmd || " close(""/tmp/env"") } { for(x in a){ if ($0~x)"
cmd = cmd || "{ gsub(x, a[x]);} } print$0 }' /tmp/" 
cmd = cmd || uid"J > /tmp/"uid"T"
call bpxwunix cmd

address tso "oget '/tmp/"uid"T' " || uid".TMP."uid"J"
cmd = "rm /tmp/ricky"
call bpxwunix cmd

"ISPEXEC VIEW DATASET('" || uid || ".TMP." || uid || "J') MACRO(RESET)"

 

ssh传输文件到z/OS并转码

2013年12月20日 13:29 | Comments(0) | Category:Other | Tags:

#! /bin/sh -x

MVS_CODEPAGE=IBM-936   # adjust to taste; perhaps IBM-936
MVS_CODEPAGE=IBM-1047  # adjust to taste; perhaps IBM-936

ssh "$MVS_USER@$MVS_HOST" "set -x
        iconv -f $MVS_CODEPAGE -t UTF-8 '$1' | 
        iconv -f ISO8859-1     -t IBM-1047"

Rexx调用ISPF命令

2013年12月06日 08:47 | Comments(0) | Category:Other | Tags:

比如打开文件后查找字符串

Main Program:

ADDRESS ISPEXEC "VPUT (FIELD) SHARED"
ADDRESS ISPEXEC "VIEW DATASET('"tabMember"') MACRO(FINDIT)"

 

FINDIT Macro:

ADDRESS ISPEXEC "VGET (FIELD) SHARED"
ADDRESS ISREDIT "MACRO"
ADDRESS ISREDIT "FIND '" || field || "'"

 

 

Rexx提交JCL的两种方式

2013年10月29日 16:30 | Comments(0) | Category:Other | Tags:

1 生成临时文件并释放到JES:

QUEUE "//FTPFILE  EXEC PGM=FTP,                                     "
QUEUE "//         PARM='hostname (EXIT'                         "
QUEUE "//SYSMDUMP DD   SYSOUT=*                                     "
QUEUE "//SYSPRINT DD   SYSOUT=*                                     "
QUEUE "//INPUT DD *                                                 "
QUEUE "username                                                          "
QUEUE "password                                                       "
QUEUE "cd /dwh                                                      "
QUEUE "prompt off                                                   "
QUEUE "locsite encoding=m                                     "      
QUEUE "locsite mbdataconn=(utf-8,utf-8)                       "      
QUEUE "locsite mbsendeol=LF                                   "      
QUEUE "put 'TSOID."||TNAME"' "||TNAME".txt                 "
QUEUE "quit                                                         "
QUEUE "/*                                                           "
      "ALLOC F(JCL) SYSOUT WRITER(INTRDR) RECFM(F B) LRECL(80)"
      "EXECIO" QUEUED() "DISKW JCL (FINIS"
      "FREE F(JCL)"                   
Return                                                              

这里顺便提一下发送utf-8编码的文件到Unix服务器要设置encoding为m(mbcs)

以及换行符为LF

 

2. 通过JES提交JCL:

QUEUE " JCL STMT"
QUEUE "/*                                                           "
QUEUE 'NN'                                                           
'SUBMIT * END(NN)'                                                   
                                                                     
RETURN                                   

 

第二种方式会将JCL所有字符转换成大写,所以调用FTP命令用第一种适用于用户名和密码对大小写敏感的情况。

VBA中调用Shell执行外部命令并返回结果

2013年10月21日 14:27 | Comments(0) | Category:Other | Tags:

要实现获取执行外部程序的结果可以用run或者exec

关于这两则的区别:http://www.codeproject.com/Tips/507798/Differences-between-Run-and-Exec-VBScript

另外稍复杂的程序可以参考这个链接

http://zhiqiang.org/blog/it/exec-shell-in-excel-vba.html

调用FTP需加-v参数才能获得所有输出:

var shell = new ActiveXObject("WScript.Shell");
    cmd = Environ("windir") & "\System32\ftp.exe -v -n -s:" & sWorkingDirectory & ftpFile
    Set oExec = oShell.exec("cmd.exe /c " & cmd)
var output = oExec..StdOut.ReadAll();

这样VBA会等待FTP执行完毕后再继续执行。

UTF-8文件上传到z/OS并转IBM-1388

2013年10月14日 09:07 | Comments(0) | Category:Other | Tags:

上传的时候加上几个选项:

quote site encoding=m
quote site mbdataconn=(utf-8,utf-8)
quote site recfm=vb (根据情况)

quote mbsendeol=LF (设置换行符)

文件格式可以是Unix或者DOS

JCL中调用USS的iconv

// JOB ICNVSAMP
// LIBDEF *,SEARCH=PRD2.SCEEBASE
// EXECEDCICONV,PARM='IFILE(''INPUT.FILE''),OFILE(''OUTPUT.FILE''),  X
FROMCODE(UTF-8),TOCODE(IBM-1388)'
/*
// EXECLISTLOG
/&

下载文件遇到Shift In Shift Out不匹配可以用

quote type b 9 s s

其他设置文件参数:

quote site recfm=vb pri=50 sec=50 cyl blksize=0
quote site lrecl=1024 recfm=fb blksize=6144 pri=100 sec=100 cyl

Excel中访问DB2 for z/OS

2013年9月27日 09:27 | Comments(0) | Category:Other | Tags:

安装一个DB2客户端,比如Data Server或者Toad for DB2

这样就可以创建ODBC数据源

通过ODBC访问并插入工作表:

Sub GetData()
    'Define SQL query string to get the CategoryName field from
    'the Category table.
    sqlstring = "SELECT * From ..."
    'Define connection string and reference File DSN.
    connstring = "ODBC;DSN=DSNName"
    'Create QueryTable in worksheet beginning with cell A1.
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("A1"), Sql:=sqlstring)
        .Refresh BackgroundQuery := False
    End With

End Sub

 

.Refresh BackgroundQuery := False将会等待查询完成再继续操作,否则在后台完成查询

 

另一种方式:

执行查询并将结果返回到数组,要用到RecordSet对象:


    'define connection and reference file DSN.
    strCon = "Driver={IBM DB2 ODBC DRIVER};Database=DBName;Hostname=" & serverAddress & ";Port=1234;Protocol=TCPIP;Uid=xxx;Pwd=xxx;"

    Set dbCon = CreateObject("ADODB.Connection")
    dbCon.Open strCon

    'define sql stmt
    strSql = "SELECT NAME From SYSIBM.SYSCOLUMNS WHERE TBNAME = '" & tabName & "' AND TBCREATOR = '" & schemaName & "' ORDER BY COLNO;"

    Set rstRecordset = CreateObject("ADODB.Recordset")
    rstRecordset.Open strSql, dbCon, 0, 1, 1

    ' Get records into array
    vArray = rstRecordset.GetRows
    Set rstRecordset = Nothing

    For i = LBound(vArray, 2) To UBound(vArray, 2)
        Msbbox vArray(0,i)
    Next i


    ' Clean up
    Erase vArray