Ricky's Blog

用PowerShell配合CSV文件批量操作：

批量修改登录名称

移动：

Move-ADObject -Identity "OU=ManagedGroups,DC=Fabrikam,DC=Com" -TargetPath "OU=Managed,DC=Fabrikam,DC=Com"

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

#!/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);

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

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什么的,比较方便

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

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

加上编译选项

CFLAGS = $(CFLAGS) -DFEAT_PROPORTIONAL_FONTS

    * 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的RID对于分区表同样是唯一值：

声明：

计算#个数

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

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)"

#! /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"

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

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 || "'"

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命令用第一种适用于用户名和密码对大小写敏感的情况。

要实现获取执行外部程序的结果可以用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执行完毕后再继续执行。

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

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

其他设置文件参数：