DB2表数据迁移db2命令db2下载db2数据库入门教

系统用到一个远程数据库。地点在USA，连接速度特别慢。DBA又不给导数据库
只好自己写一个数据库表迁移的方法。但是只能一次一张表慢慢的导。使用PHP编写

//原数据库$databaseOri = '原数据库名称';
    $hostnameOri = 'IP地址';
    $portOri = '端口号';
    $userOri = '用户名';
    $passwordOri = '密码';
    $csOri="DATABASE=$databaseOri;HOSTNAME=$hostnameOri;PORT=$portOri;PROTOCOL=TCPIP;UID=$userOri;PWD=$passwordOri;";
    $dbOri = db2_connect ($csOri, $userOri, $passwordOri);
    //目标数据库$databaseDes = '要转移到的数据库的名称';
    $hostnameDes = 'IP地址';
    $portDes = '端口号';
    $userDes = '用户名';
    $passwordDes = '密码';
    $csDes="DATABASE=$databaseDes;HOSTNAME=$hostnameDes;PORT=$portDes;PROTOCOL=TCPIP;UID=$userDes;PWD=$passwordDes;";
    $dbDes = db2_connect ($csDes, $userDes, $passwordDes);

    //执行sql的方法functionfetcher($db, $query,$par = array()){$stmt = db2_prepare($db, $query);
        $res=array();
        if($stmt) {
          //print_r($stmt);$ex = db2_execute($stmt,$par);
          if($ex) {
            try{
                while($row = db2_fetch_assoc($stmt)) {
                    array_push($res, $row);
                } 
            }catch(Exception$e){}
          }else{
            print_r($query);
          }
        }
        return$res;
    }

    //插入数据库的方法functioninsertIntoDes($db, $query,$par = array()){$stmt = db2_prepare($db, $query);
        $res=array();
        if($stmt) {
          $ex = db2_execute($stmt,$par);
          if(!$ex){
            print_r($query);
          }
        }
        return$res;
    }

    functiontransferDB($tableName,$dbOri,$dbDes) {//拼出要转移的table的所有字段名$tableCloums = fetcher(
            $dbOri,
            "select NAME from sysibm.syscolumns where tbname='".$tableName."'",
            array()
        );
        $tableParams = "";
        $insertParamsLength = count($tableCloums);
        for($temp=0;$temp<$insertParamsLength;$temp++){
            $tmpName = $tableCloums[$temp]["NAME"];
            $tableParams = $tableParams.$tmpName;
            //print_r($tableCloums[$temp]["NAME"]);if($temp < $insertParamsLength - 1 ){
                $tableParams = $tableParams.",";
            }
        }
        //找出table的主键$resultKeyArray = fetcher(
            $dbOri,
            "select NAME from sysibm.syscolumns where tbname='".$tableName."' and keyseq = '1'",
            array()
        );
        if($resultKeyArray[0]["NAME"]){
            $keyCloum = $resultKeyArray[0]["NAME"];
        }else{
            $keyCloum = "ID";
        }


        //获取表的所有行数，因为如果表太大有几百万行的话，一下全导入到内存中会照成内存溢出$count = fetcher(
            $dbOri,
            "select COUNT(*) from public.".$tableName,
            array()
        );
        $dataCount = $count[0][1];
        //确定要执行几次$executeloops = floor($dataCount / 10000 + 1);
        //$executeloops = 1;//循环执行for($i=0;$i<$executeloops;$i++){
            $pageIndex = $i;
            $beginIndex = $i*10000;
            $endIndex = ($i+1) * 10000;
            //通过分页查询，每次从原表中获取1万条数据$getDataFromOriTab = 
            "select ".$tableParams." from (
                select ROW_NUMBER() OVER(ORDER BY ".$keyCloum." DESC) AS ROWNUM, ".$tableParams." from public.".$tableName."  
            ) a where ROWNUM > ".$beginIndex." and ROWNUM <= ".$endIndex ;
            //fwrite($myfile, $getDataFromOriTab);$tmpData = fetcher($dbOri,$getDataFromOriTab,array());
            //拼接出insert语句中字段的value的值$valueStr = "";
            foreach($tmpDataas$index => $row){
                $valueStr = "";
                foreach ($rowas$cloumName => $value) {
                    if(empty($value)){
                        $valueStr = $valueStr . "'',";
                    }elseif (is_null($value)) {
                        $valueStr = $valueStr . "null,";
                    }else{
                        $valueStr = $valueStr . "$value,";
                    }
                }
                $valueStr = substr($valueStr, 0, -1);
                $insertSql = "INSERT INTO public.".$tableName." (".$tableParams.") VALUES (".$valueStr.");";
                insertIntoDes($dbDes,$insertSql,array()); 
            }
        }

    }
    //参数为要导的表的表名
    transferDB("表名",$dbOri,$dbDes);
?>

').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('

').text(i)); }; $numbering.fadeIn(1700); }); });

以上就介绍了DB2表数据迁移，包括了db2,迁移方面的内容，希望对PHP教程有兴趣的朋友有所帮助。