예제 #1
0
        // execute DB2 SQL command
        public DataTable ExecSQL(string sqlString)
        {
            using (var sqlCmd = Db2Client.CreateCommand()){
                sqlCmd.CommandText = sqlString;
                Console.WriteLine(sqlCmd.CommandText + '\n');

                DataTable tbl = new DataTable();
                using (var dbReader = sqlCmd.ExecuteReader()){
                    tbl.Load(dbReader);
                }
                return(tbl);
            }
        }
예제 #2
0
        public void Connect()
        {
            Console.Write($"Establishing connection to {Profile.Host}...");

            ClientUtils.ConnectWithRetry(SshClient, Profile.ConnectAttempts);
            Console.Write("SSH...");

            ClientUtils.ConnectWithRetry(SftpClient, Profile.ConnectAttempts);
            Console.Write("SFTP...");

            Db2Client.Open();
            Console.Write("DB2...");
            Console.WriteLine("Connected.");

            if (!SftpClient.Exists(IfsCache))
            {
                Console.WriteLine($"Creating IFS cache at {IfsCache}.");
                ExecShell($"mkdir -p \"{IfsCache}\""); // swap out for SQL QCMDEXC?
            }
        }
예제 #3
0
        // Get list of libraries on host
        public List <Library> GetLibraries()
        {
            var libs      = new List <Library>();
            var sqlString = $@"
                select SYSTEM_SCHEMA_NAME, coalesce(SCHEMA_TEXT,'')
                from QSYS2.SYSSCHEMAS
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering list of libraries on {Profile.Host}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        libs.Add(SqlUtils.RowToLib(dbReader));
                    }
                }
            }
            return(libs);
        }
예제 #4
0
        // Get list of source physical files in LIB
        public List <SrcPf> GetSrcPfList(string lib)
        {
            var spfs      = new List <SrcPf>();
            var sqlString = $@"
                select SYSTEM_TABLE_NAME, coalesce(TABLE_TEXT,'')
                from QSYS2.SYSTABLES
                where SYSTEM_TABLE_SCHEMA='{lib}' and FILE_TYPE='S' and TABLE_TYPE='P'
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering source physical file list of library {lib}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        spfs.Add(SqlUtils.RowToSrcPf(dbReader));
                    }
                }
            }
            return(spfs);
        }
예제 #5
0
        // Get details of source physical file
        public SrcPf GetSrcPfDetails(string lib, string spf)
        {
            var sqlString = $@"
                select SYSTEM_TABLE_NAME, coalesce(TABLE_TEXT,'')
                from QSYS2.SYSTABLES
                where SYSTEM_TABLE_SCHEMA='{lib}' and FILE_TYPE='S' and TABLE_TYPE='P' and SYSTEM_TABLE_NAME='{spf}'
                limit 1
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering details on source physical file {lib}/{spf}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        return(SqlUtils.RowToSrcPf(dbReader));
                    }
                }
            }
            throw new KanpachiQsysException($"Could not find source physical file at {lib}/{spf}.");
        }
예제 #6
0
        // Get details of library
        public Library GetLibraryDetails(string lib)
        {
            var sqlString = $@"
                select SYSTEM_SCHEMA_NAME, coalesce(SCHEMA_TEXT,'')
                from QSYS2.SYSSCHEMAS
                where SYSTEM_SCHEMA_NAME = '{lib}'
                limit 1
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering details on library {lib}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        return(SqlUtils.RowToLib(dbReader));
                    }
                }
            }
            throw new KanpachiQsysException($"Could not find library {lib}.");
        }
예제 #7
0
        // Get details of source member => type, size, record length, etc.
        public SrcMbr GetSrcMbrDetails(string lib, string spf, string mbr)
        {
            var sqlString = $@"
                select TABLE_PARTITION, coalesce(SOURCE_TYPE,''), NUMBER_ROWS, coalesce(PARTITION_TEXT,''),
                  AVGROWSIZE, DATA_SIZE, CREATE_TIMESTAMP, LAST_CHANGE_TIMESTAMP
                from QSYS2.SYSPARTITIONSTAT
                where SYSTEM_TABLE_SCHEMA='{lib}' and SYSTEM_TABLE_NAME='{spf}' and TABLE_PARTITION='{mbr}'
                limit 1
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering details on source member {lib}/{spf}/{mbr}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        return(SqlUtils.RowToSrcMbr(dbReader));
                    }
                }
            }
            throw new KanpachiQsysException($"Could not find source member at {lib}/{spf}/{mbr}.");
        }
예제 #8
0
        // Get list of source members at LIB/SPF
        public List <SrcMbr> GetSrcMbrList(string lib, string spf)
        {
            var members   = new List <SrcMbr>();
            var sqlString = $@"
                select TABLE_PARTITION, coalesce(SOURCE_TYPE,''), NUMBER_ROWS, coalesce(PARTITION_TEXT,''),
                  AVGROWSIZE, DATA_SIZE, CREATE_TIMESTAMP, LAST_CHANGE_TIMESTAMP
                from QSYS2.SYSPARTITIONSTAT
                where SYSTEM_TABLE_SCHEMA='{lib}' and SYSTEM_TABLE_NAME='{spf}'
                order by 1
            ";

            using (var sqlCmd = Db2Client.CreateCommand()){
                Console.WriteLine($"Gathering member list of source physical file {lib}/{spf}...\n{sqlString}\n");
                sqlCmd.CommandText = sqlString;
                using (var dbReader = sqlCmd.ExecuteReader()){
                    while (dbReader.Read())
                    {
                        members.Add(SqlUtils.RowToSrcMbr(dbReader));
                    }
                }
            }
            return(members);
        }
예제 #9
0
 public void Disconnect()
 {
     SshClient?.Disconnect();
     SftpClient?.Disconnect();
     Db2Client?.Close();
 }