Data mismatch while migrating data from SAS to Teradata using TPT

Tools
Enthusiast

Data mismatch while migrating data from SAS to Teradata using TPT

Hi,

I have been trying to load a 200GB dataset from SAS onto Teradata using TPT multiload option. The load was successful but i noted a record count mismatch in SAS and Teradata table. What could be the reasons for this? There was no records captured in UV & ET tables. Also suggest any apporach to identifiy these mismatch records. 

9 REPLIES
Teradata Employee

Re: Data mismatch while migrating data from SAS to Teradata using TPT

There could be several reasons. But before I try to answer the questions, the best thing to do is to provide me with the entire output from the TPT job. It will tell us how many rows were actually sent to Teradata and other information which might point to where the discrepencies are.

The number of rows in the source table (that you think needed to be moved) would also be helpful.

-- SteveF
Enthusiast

Re: Data mismatch while migrating data from SAS to Teradata using TPT

Hi,

Find below the script and the log of this run. This log also has the sql to populate the missing rows (or the data mismatch rows)  into an error table (TABLE1_ERR). This query is also failing during this run.

=========================================================================

script:

=====

LIBNAME TD TERADATA USER=USER1 PASSWORD=xxxxxxxx SERVER="xx.xxx.xxx.xx" DATABASE=STG_DB;

OPTIONS DBIDIRECTEXEC;

libname cisdata "/COPY/isdata_subsets";

option msglevel=i nostsuffix sastrace=',,,s' sastraceloc=saslog

sql_ip_trace=source;

option dbfmtignore dbidirectexec nofmterr sqlgeneration=dbmust

sqlmapputto=sas_put;

options fullstimer;

options sastrace=',,,d' sastraceloc=saslog;

proc sql;

insert into TD.TABLE1

(

    TPT=YES MULTILOAD=YES

    TPT_MIN_SESSIONS=1

    TPT_MAX_SESSIONS=15

)

select

COL1                           

,COL2

,COL3

,COL4

,COL5

,COL6

,COL7

,COL8

,COL9

,COL10

,COL11

,COL12

,COL13

,COL14                      

,COL15

,COL16

,COL17

,COL18

,COL19

,COL20                    

,COL21

,COL22

,COL23

,COL24

,COL25

,COL26                      

,COL27

,COL28

,COL29                   

,COL30           

,2012

from CISDATA.TABLE1

;

insert into TD.TABLE1_ERR (FASTLOAD=YES SESSIONS=20)

SELECT

T1.COL1

,T1.COL2

,T1.COL3

,T1.COL4

,T1.COL5

,T1.COL6

,T1.COL7

,T1.COL8

,T1.COL9

,T1.COL10

,T1.COL11

,T1.COL12

,T1.COL13

,T1.COL14

,T1.COL15

,T1.COL16

,T1.COL17

,T1.COL18

,T1.COL19

,T1.COL20

,T1.COL21

,T1.COL22

,T1.COL23

,T1.COL24

,T1.COL25

,T1.COL26

,T1.COL27

,T1.COL28

,T1.COL29

,T1.COL30

FROM CISDATA.TABLE1 T1

LEFT OUTER JOIN

TD.TABLE1 T2

ON T1.COL5=T2.COL5

WHERE

T2.COL5 IS NULL

;

quit;

Log of this run:

===========

1 The SAS System                               06:07 Sunday, March 16, 2014

NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2)

NOTE: This session is executing on the Linux 2.6.32-431.3.1.el6.x86_64 (LIN X64) platform.

NOTE: Enhanced analytical products:

SAS/STAT 12.1

You are running SAS 9. Some SAS 8 files will be automatically converted

by the V9 engine; others are incompatible.  Please see

http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is

recommended for converting all your SAS libraries from any

SAS 8 release to SAS 9.  For details and examples, please see

http://support.sas.com/rnd/migration/index.html

This message is contained in the SAS news file, and is presented upon

initialization.  Edit the file "news" in the "misc/base" directory to

display site-specific news and information in the program log.

The command line option "-nonews" will prevent this display.

NOTE: SAS initialization used:

      real time           0.33 seconds

      cpu time            0.02 seconds

NOTE: AUTOEXEC processing beginning; file is /phcommon/code/autoexec.sas.

NOTE: Libref IGS12M06 was successfully assigned as follows:

      Engine:        V9

      Physical Name: /data/smart06

NOTE: Libref IGS12M08 was successfully assigned as follows:

      Engine:        V9

      Physical Name: /data//smart08

NOTE: Libref LIBRARY was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/ref/fmt

NOTE: Libref HIF_TEMP was successfully assigned as follows:

      Engine:        V9

      Physical Name: /temp

NOTE: Libref SYSMART was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/ref/dmart

NOTE: Libref SCMACRO was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/test_macro_lib/testmacro

NOTE: Libref HESFMT was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/load/sas/load_template/formats

2                                                          The SAS System                               06:07 Sunday, March 16, 2014

NOTE: AUTOEXEC processing completed.

1          LIBNAME TD TERADATA USER=USER1 PASSWORD=XXXXXXXXXX SERVER="xx.xxx.xxx.xx" DATABASE=STG_DB;

NOTE: Libref TD was successfully assigned as follows:

      Engine:        TERADATA

      Physical Name: xx.xxx.xxx.xx

2          

3          

4          OPTIONS DBIDIRECTEXEC;

5          

6          libname cisdata "/COPY/isdata_subsets";

NOTE: Libref CISDATA was successfully assigned as follows:

      Engine:        V9

      Physical Name: /COPY/isdata_subsets

7          

8          

9          

10         

11         option msglevel=i nostsuffix sastrace=',,,s' sastraceloc=saslog

12         sql_ip_trace=source;

13         

14         option dbfmtignore dbidirectexec nofmterr sqlgeneration=dbmust

15         sqlmapputto=sas_put;

16         

17         

18         

19         options fullstimer;

20         

21         options sastrace=',,,d' sastraceloc=saslog;

22         

23         

24         

25         proc sql;

26         

27         

28         insert into TD.TABLE1

29         (

30             TPT=YES MULTILOAD=YES

31             TPT_MIN_SESSIONS=1

32             TPT_MAX_SESSIONS=15

33         )

34         select

35         COL1

36         ,COL2

37         ,COL3

38         ,COL4

39         ,COL5

40         ,COL6

41         ,COL7

42         ,COL8

43         ,COL9

44         ,COL10

45         ,COL11

46         ,COL12

47         ,COL13

48         ,COL14

49         ,COL15

50         ,COL16

3                                                          The SAS System                               06:07 Sunday, March 16, 2014

51         ,COL17

52         ,COL18

53         ,COL19

54         ,COL20

55         ,COL21

56         ,COL22

57         ,COL23

58         ,COL24

59         ,COL25

60         ,COL26

61         ,COL27

62         ,COL28

63         ,COL29

64         ,COL30

65         ,2012

66         from CISDATA.TABLE1

67         ;

TERADATA_0: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_1: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: 591288139 rows were inserted into TD.TABLE1.

TERADATA: trforc: COMMIT WORK

68         insert into TD.TABLE1_ERR (FASTLOAD=YES SESSIONS=20)

69         SELECT

70         T1.COL1

71         ,T1.COL2

72         ,T1.COL3

73         ,T1.COL4

74         ,T1.COL5

75         ,T1.COL6

76         ,T1.COL7

77         ,T1.COL8

78         ,T1.COL9

79         ,T1.COL10

80         ,T1.COL11

81         ,T1.COL12

82         ,T1.COL13

83         ,T1.COL14

84         ,T1.COL15

85         ,T1.COL16

86         ,T1.COL17

87         ,T1.COL18

88         ,T1.COL19

89         ,T1.COL20

90         ,T1.COL21

91         ,T1.COL22

92         ,T1.COL23

93         ,T1.COL24

94         ,T1.COL25

4                                                          The SAS System                               06:07 Sunday, March 16, 2014

95         ,T1.COL26

96         ,T1.COL27

97         ,T1.COL28

98         ,T1.COL29

99         ,T1.COL30

100        FROM CISDATA.TABLE1 T1

101        LEFT OUTER JOIN

102        TD.TABLE1 T2

103        ON T1.COL5=T2.COL5

104        WHERE

105        T2.COL5 IS NULL

106        ;

TERADATA_2: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_3: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_4: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_5: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

NOTE: SAS threaded sort was used.

TERADATA: trqacol- No casting. Raw row size=10, Casted size=10, CAST_OVERHEAD_MAXPERCENT=20%

TERADATA_6: Prepared: on connection 2

SELECT "COL5" FROM STG_DB."TABLE1"

ERROR: Teradata prepare: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost. SQL statement was: SELECT "COL5" FROM

       STG_DB."TABLE1" .

ERROR: PROC SQL runtime error for operation=sqxsrc.

ERROR: An error has occurred.

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

107        quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:10:49.27

      user cpu time       1:06:02.68

      system cpu time     30:06.61

      memory              265945.73k

5                                                          The SAS System                               06:07 Sunday, March 16, 2014

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       23

      Page Reclaims                     132698

      Page Swaps                        0

      Voluntary Context Switches        18277898

      Involuntary Context Switches      14349900

      Block Input Operations            726750456

      Block Output Operations           518342256

ERROR: Errors printed on page 4.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

NOTE: The SAS System used:

      real time           4:10:50.78

      user cpu time       1:06:02.78

      system cpu time     30:06.66

      memory              270981.82k

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       43

      Page Reclaims                     139890

      Page Swaps                        0

      Voluntary Context Switches        18278062

      Involuntary Context Switches      14349909

      Block Input Operations            726754264

      Block Output Operations           518342552

Enthusiast

Re: Data mismatch while migrating data from SAS to Teradata using TPT

The number of rows in the source table is 591290000.

Looks like the log hasn't been completely published. Find below the same:

1 The SAS System                               06:07 Sunday, March 16, 2014

NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2)

NOTE: This session is executing on the Linux 2.6.32-431.3.1.el6.x86_64 (LIN X64) platform.

NOTE: Enhanced analytical products:

SAS/STAT 12.1

You are running SAS 9. Some SAS 8 files will be automatically converted

by the V9 engine; others are incompatible.  Please see

http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is

recommended for converting all your SAS libraries from any

SAS 8 release to SAS 9.  For details and examples, please see

http://support.sas.com/rnd/migration/index.html

This message is contained in the SAS news file, and is presented upon

initialization.  Edit the file "news" in the "misc/base" directory to

display site-specific news and information in the program log.

The command line option "-nonews" will prevent this display.

NOTE: SAS initialization used:

      real time           0.33 seconds

      cpu time            0.02 seconds

NOTE: AUTOEXEC processing beginning; file is /phcommon/code/autoexec.sas.

NOTE: Libref IGS12M06 was successfully assigned as follows:

      Engine:        V9

      Physical Name: /data/smart06

NOTE: Libref IGS12M08 was successfully assigned as follows:

      Engine:        V9

      Physical Name: /data//smart08

NOTE: Libref LIBRARY was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/ref/fmt

NOTE: Libref HIF_TEMP was successfully assigned as follows:

      Engine:        V9

      Physical Name: /temp

NOTE: Libref SYSMART was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/ref/dmart

NOTE: Libref SCMACRO was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/test_macro_lib/testmacro

NOTE: Libref HESFMT was successfully assigned as follows:

      Engine:        V9

      Physical Name: /analysis/load/sas/load_template/formats

2                                                          The SAS System                               06:07 Sunday, March 16, 2014

NOTE: AUTOEXEC processing completed.

1          LIBNAME TD6700 TERADATA USER=USER1 PASSWORD=XXXXXXXXXX SERVER="xx.xxx.xxx.xx" DATABASE=STG_DB;

NOTE: Libref TD6700 was successfully assigned as follows:

      Engine:        TERADATA

      Physical Name: xx.xxx.xxx.xx

2          

3          

4          OPTIONS DBIDIRECTEXEC;

5          

6          libname cisdata "/COPY/isdata_subsets";

NOTE: Libref CISDATA was successfully assigned as follows:

      Engine:        V9

      Physical Name: /COPY/isdata_subsets

7          

8          

9          

10         

11         option msglevel=i nostsuffix sastrace=',,,s' sastraceloc=saslog

12         sql_ip_trace=source;

13         

14         option dbfmtignore dbidirectexec nofmterr sqlgeneration=dbmust

15         sqlmapputto=sas_put;

16         

17         

18         

19         options fullstimer;

20         

21         options sastrace=',,,d' sastraceloc=saslog;

22         

23         

24         

25         proc sql;

26         

27         

28         insert into TD6700.TABLE1

29         (

30             TPT=YES MULTILOAD=YES

31             TPT_MIN_SESSIONS=1

32             TPT_MAX_SESSIONS=15

33         )

34         select

35         COL1

36         ,COL2

37         ,COL3

38         ,COL4

39         ,COL5

40         ,COL6

41         ,COL7

42         ,COL8

43         ,COL9

44         ,COL10

45         ,COL11

46         ,COL12

47         ,COL13

48         ,COL14

49         ,COL15

50         ,COL16

3                                                          The SAS System                               06:07 Sunday, March 16, 2014

51         ,COL17

52         ,COL18

53         ,COL19

54         ,COL20

55         ,COL21

56         ,COL22

57         ,COL23

58         ,COL24

59         ,COL25

60         ,COL26

61         ,COL27

62         ,COL28

63         ,COL29

64         ,COL30

65         ,2012

66         from cisdata.TABLE1

67         ;

TERADATA_0: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_1: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: 591288139 rows were inserted into TD6700.TABLE1.

TERADATA: trforc: COMMIT WORK

68         insert into TD6700.TABLE1_ERR (FASTLOAD=YES SESSIONS=20)

69         SELECT

70         T1.COL1

71         ,T1.COL2

72         ,T1.COL3

73         ,T1.COL4

74         ,T1.COL5

75         ,T1.COL6

76         ,T1.COL7

77         ,T1.COL8

78         ,T1.COL9

79         ,T1.COL10

80         ,T1.COL11

81         ,T1.COL12

82         ,T1.COL13

83         ,T1.COL14

84         ,T1.COL15

85         ,T1.COL16

86         ,T1.COL17

87         ,T1.COL18

88         ,T1.COL19

89         ,T1.COL20

90         ,T1.COL21

91         ,T1.COL22

92         ,T1.COL23

93         ,T1.COL24

94         ,T1.COL25

4                                                          The SAS System                               06:07 Sunday, March 16, 2014

95         ,T1.COL26

96         ,T1.COL27

97         ,T1.COL28

98         ,T1.COL29

99         ,T1.COL30

100        FROM CISDATA.TABLE1 T1

101        LEFT OUTER JOIN

102        TD6700.TABLE1 T2

103        ON T1.COL5=T2.COL5

104        WHERE

105        T2.COL5 IS NULL

106        ;

TERADATA_2: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_3: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_4: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_5: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

NOTE: SAS threaded sort was used.

TERADATA: trqacol- No casting. Raw row size=10, Casted size=10, CAST_OVERHEAD_MAXPERCENT=20%

TERADATA_6: Prepared: on connection 2

SELECT "COL5" FROM STG_DB."TABLE1"

ERROR: Teradata prepare: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost. SQL statement was: SELECT "COL5" FROM

       STG_DB."TABLE1" .

ERROR: PROC SQL runtime error for operation=sqxsrc.

ERROR: An error has occurred.

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

107        quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:10:49.27

      user cpu time       1:06:02.68

      system cpu time     30:06.61

      memory              265945.73k

5                                                          The SAS System                               06:07 Sunday, March 16, 2014

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       23

      Page Reclaims                     132698

      Page Swaps                        0

      Voluntary Context Switches        18277898

      Involuntary Context Switches      14349900

      Block Input Operations            726750456

      Block Output Operations           518342256

ERROR: Errors printed on page 4.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

NOTE: The SAS System used:

      real time           4:10:50.78

      user cpu time       1:06:02.78

      system cpu time     30:06.66

      memory              270981.82k

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       43

      Page Reclaims                     139890

      Page Swaps                        0

      Voluntary Context Switches        18278062

      Involuntary Context Switches      14349909

      Block Input Operations            726754264

      Block Output Operations           518342552

Teradata Employee

Re: Data mismatch while migrating data from SAS to Teradata using TPT

This looks like SAS is using DataStage to load the data, which also means the job is using TPTAPI, and not script-based TPT (correct me if I am wrong).

The information you sent me was the SA (or DataStage) information and I am unfamiliar with those tools to be of any help there.

I need to see any type of logging from the TPTAPI operators, and if there isn't any then you must enable tracing in TPTAPI and capture the output so that I can see what the TPTAPI operators are doing, and the number of rows they think they are loading.

With TPTAPI, the operators will send all rows they receive from the application to Teradata. If rows are missing from the target table, you might need to see if DataStage is somehow filtering some rows.

Also, check to see if they might be any duplicate rows in the source table that will not make it into the target table.

Is the target table a SET table oir MULTISET table?

-- SteveF
Enthusiast

Re: Data mismatch while migrating data from SAS to Teradata using TPT

Please find out the entire row level duplicates in source. This count should explain why the records less in target table, as you are using the fast load functionality. (Fast load can't support row level duplicates even the target it SET table)

Regards,

Sri

Teradata Employee

Re: Data mismatch while migrating data from SAS to Teradata using TPT

The original post said:

"I have been trying to load a 200GB dataset from SAS onto Teradata using TPT multiload option."

Thus, they may be using the Update operator.

One of their INSERTs reference MULTILOAD=YES, the other one says FASTLOAD=YES.

So, the customer will have to verify.

-- SteveF
Enthusiast

Re: Data mismatch while migrating data from SAS to Teradata using TPT

My target table is a MULTISET table.

This first INSERT in the script (which uses TPT MULTILOAD) is to load the data from SAS dataset onto Teradata target.

The second INSERT (which uses TPT FASTLOAD) is to identifty the mismatch records & insert them into an error table with a LEFT OUTER JOIN between the SAS dataset and the Teradata target table. But this second INSERT failed as shown in the log due to network connection being lost.

Log has not been completely published above. Find below the remaining log contents of this run:

NOTE: AUTOEXEC processing completed.

1          LIBNAME TD6700 TERADATA USER=USER1 PASSWORD=XXXXXXXXXX SERVER="xx.xxx.xxx.xx" DATABASE=STG_DB;

NOTE: Libref TD6700 was successfully assigned as follows:

      Engine:        TERADATA

      Physical Name: xx.xxx.xxx.xx

2          

3          

4          OPTIONS DBIDIRECTEXEC;

5          

6          libname cisdata "/COPY/isdata_subsets";

NOTE: Libref CISDATA was successfully assigned as follows:

      Engine:        V9

      Physical Name: /COPY/isdata_subsets

7          

8          

9          

10         

11         option msglevel=i nostsuffix sastrace=',,,s' sastraceloc=saslog

12         sql_ip_trace=source;

13         

14         option dbfmtignore dbidirectexec nofmterr sqlgeneration=dbmust

15         sqlmapputto=sas_put;

16         

17         

18         

19         options fullstimer;

20         

21         options sastrace=',,,d' sastraceloc=saslog;

22         

23         

24         

25         proc sql;

26         

27         

28         insert into TD6700.TABLE1

29         (

30             TPT=YES MULTILOAD=YES

31             TPT_MIN_SESSIONS=1

32             TPT_MAX_SESSIONS=15

33         )

34         select

35         COL1

36         ,COL2

37         ,COL3

38         ,COL4

39         ,COL5

40         ,COL6

41         ,COL7

42         ,COL8

43         ,COL9

44         ,COL10

45         ,COL11

46         ,COL12

47         ,COL13

48         ,COL14

49         ,COL15

50         ,COL16

3                                                          The SAS System                               06:07 Sunday, March 16, 2014

51         ,COL17

52         ,COL18

53         ,COL19

54         ,COL20

55         ,COL21

56         ,COL22

57         ,COL23

58         ,COL24

59         ,COL25

60         ,COL26

61         ,COL27

62         ,COL28

63         ,COL29

64         ,COL30

65         ,2012

66         from cisdata.TABLE1

67         ;

TERADATA_0: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_1: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: 591288139 rows were inserted into TD6700.TABLE1.

TERADATA: trforc: COMMIT WORK

68         insert into TD6700.TABLE1_ERR (FASTLOAD=YES SESSIONS=20)

69         SELECT

70         T1.COL1

71         ,T1.COL2

72         ,T1.COL3

73         ,T1.COL4

74         ,T1.COL5

75         ,T1.COL6

76         ,T1.COL7

77         ,T1.COL8

78         ,T1.COL9

79         ,T1.COL10

80         ,T1.COL11

81         ,T1.COL12

82         ,T1.COL13

83         ,T1.COL14

84         ,T1.COL15

85         ,T1.COL16

86         ,T1.COL17

87         ,T1.COL18

88         ,T1.COL19

89         ,T1.COL20

90         ,T1.COL21

91         ,T1.COL22

92         ,T1.COL23

93         ,T1.COL24

94         ,T1.COL25

4                                                          The SAS System                               06:07 Sunday, March 16, 2014

95         ,T1.COL26

96         ,T1.COL27

97         ,T1.COL28

98         ,T1.COL29

99         ,T1.COL30

100        FROM CISDATA.TABLE1 T1

101        LEFT OUTER JOIN

102        TD6700.TABLE1 T2

103        ON T1.COL5=T2.COL5

104        WHERE

105        T2.COL5 IS NULL

106        ;

TERADATA_2: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_3: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_4: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_5: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

NOTE: SAS threaded sort was used.

TERADATA: trqacol- No casting. Raw row size=10, Casted size=10, CAST_OVERHEAD_MAXPERCENT=20%

TERADATA_6: Prepared: on connection 2

SELECT "COL5" FROM STG_DB."TABLE1"

ERROR: Teradata prepare: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost. SQL statement was: SELECT "COL5" FROM

       STG_DB."TABLE1" .

ERROR: PROC SQL runtime error for operation=sqxsrc.

ERROR: An error has occurred.

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

107        quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:10:49.27

      user cpu time       1:06:02.68

      system cpu time     30:06.61

      memory              265945.73k

5                                                          The SAS System                               06:07 Sunday, March 16, 2014

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       23

      Page Reclaims                     132698

      Page Swaps                        0

      Voluntary Context Switches        18277898

      Involuntary Context Switches      14349900

      Block Input Operations            726750456

      Block Output Operations           518342256

ERROR: Errors printed on page 4.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

NOTE: The SAS System used:

      real time           4:10:50.78

      user cpu time       1:06:02.78

      system cpu time     30:06.66

      memory              270981.82k

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       43

      Page Reclaims                     139890

      Page Swaps                        0

      Voluntary Context Switches        18278062

      Involuntary Context Switches      14349909

      Block Input Operations            726754264

      Block Output Operations           518342552

Enthusiast

Re: Data mismatch while migrating data from SAS to Teradata using TPT

51         ,COL17

52         ,COL18

53         ,COL19

54         ,COL20

55         ,COL21

56         ,COL22

57         ,COL23

58         ,COL24

59         ,COL25

60         ,COL26

61         ,COL27

62         ,COL28

63         ,COL29

64         ,COL30

65         ,2012

66         from cisdata.TABLE1

67         ;

TERADATA_0: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_1: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

NOTE: 591288139 rows were inserted into TD6700.TABLE1.

TERADATA: trforc: COMMIT WORK

68         insert into TD6700.TABLE1_ERR (FASTLOAD=YES SESSIONS=20)

69         SELECT

70         T1.COL1

71         ,T1.COL2

72         ,T1.COL3

73         ,T1.COL4

74         ,T1.COL5

75         ,T1.COL6

76         ,T1.COL7

77         ,T1.COL8

78         ,T1.COL9

79         ,T1.COL10

80         ,T1.COL11

81         ,T1.COL12

82         ,T1.COL13

83         ,T1.COL14

84         ,T1.COL15

85         ,T1.COL16

86         ,T1.COL17

87         ,T1.COL18

88         ,T1.COL19

89         ,T1.COL20

90         ,T1.COL21

91         ,T1.COL22

92         ,T1.COL23

93         ,T1.COL24

94         ,T1.COL25

95         ,T1.COL26

96         ,T1.COL27

97         ,T1.COL28

98         ,T1.COL29

99         ,T1.COL30

100        FROM CISDATA.TABLE1 T1

101        LEFT OUTER JOIN

102        TD6700.TABLE1 T2

103        ON T1.COL5=T2.COL5

104        WHERE

105        T2.COL5 IS NULL

106        ;

TERADATA_2: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_3: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

TERADATA_4: Prepared: on connection 1

SELECT * FROM STG_DB."TABLE1_ERR"

TERADATA_5: Prepared: on connection 2

SELECT * FROM STG_DB."TABLE1"

NOTE: SAS threaded sort was used.

TERADATA: trqacol- No casting. Raw row size=10, Casted size=10, CAST_OVERHEAD_MAXPERCENT=20%

TERADATA_6: Prepared: on connection 2

SELECT "COL5" FROM STG_DB."TABLE1"

ERROR: Teradata prepare: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost. SQL statement was: SELECT "COL5" FROM

       STG_DB."TABLE1" .

ERROR: PROC SQL runtime error for operation=sqxsrc.

ERROR: An error has occurred.

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

TERADATA: trforc: COMMIT WORK

TERADATA: trforc: COMMIT WORK  failed

ERROR: ERROR: Commit: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

    ROLLBACK has been issued (Any rows processed after the last COMMIT are lost).

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

107        quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:10:49.27

      user cpu time       1:06:02.68

      system cpu time     30:06.61

      memory              265945.73k

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       23

      Page Reclaims                     132698

      Page Swaps                        0

      Voluntary Context Switches        18277898

      Involuntary Context Switches      14349900

      Block Input Operations            726750456

      Block Output Operations           518342256

ERROR: Errors printed on page 4.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

NOTE: The SAS System used:

      real time           4:10:50.78

      user cpu time       1:06:02.78

      system cpu time     30:06.66

      memory              270981.82k

      OS Memory           272940.00k

      Timestamp           03/16/2014 10:18:35 AM

      Page Faults                       43

      Page Reclaims                     139890

      Page Swaps                        0

      Voluntary Context Switches        18278062

      Involuntary Context Switches      14349909

      Block Input Operations            726754264

      Block Output Operations           518342552

Teradata Employee

Re: Data mismatch while migrating data from SAS to Teradata using TPT

Again, I cannot help until I have more detailed information about the TPT operator.

I need verification that the job is using a tool such as DataStage to run the TPT operators (via TPTAPI).

I then need some type of tracing diagnostics for the TPT operators.

The information you are providing might be ok for some, but not when trying to diagnose a TPT task.

(For example, the diagnostic information will tell me exactly how many rows were given to the operator to send to Teradata. That might be a clue as to what is going on.)

-- SteveF