cancel
Showing results for 
Search instead for 
Did you mean: 

Bad performance for DB2 during R3load export

0 Kudos

 

The below is the background and analysis detail regarding this performance issue.

SB1 Unicode Conversion will be done by SAP tools R3load, which means using R3load to export the whole database to filesystem and import them into new SB1 at la later time.

Currently, R3load-Export has run for the long time and not finished yet.

In SB1 database, there’s a table /BIC/B0000452000 with around 550GB which is a one of the lastest table. So performance optimization on this table must be taken to speed up the R3load process.

Here’s the steps I have taken on optimization for this table.

  • Using R3load to split this table into 550 packages(1 GB for each package) which is standard optimization method. And then set number of parallel processes to 30 which means 30 R3load processes are running in parallel and each process for each package.

arlssap03:sb1adm 638> ps -ef|grep R3load

  sb1adm  7078032 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-109.cmd -datacodepage 4102 -l _BIC_B0000452000-109.log -stop_on_error

  sb1adm  7274566 24182820  14 02:55:54  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-105.cmd -datacodepage 4102 -l _BIC_B0000452000-105.log -stop_on_error

  sb1adm 11010136 24182820   0 02:55:55  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-112.cmd -datacodepage 4102 -l _BIC_B0000452000-112.log -stop_on_error

  sb1adm 11469032 24182820   0 04:56:21  pts/4  0:35 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-120.cmd -datacodepage 4102 -l _BIC_B0000452000-120.log -stop_on_error

  sb1adm 11927680 24182820   3 02:55:52  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-93.cmd -datacodepage 4102 -l _BIC_B0000452000-93.log -stop_on_error

  sb1adm 12124170 24182820   2 02:55:50  pts/4  0:41 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-92.cmd -datacodepage 4102 -l _BIC_B0000452000-92.log -stop_on_error

  sb1adm 13238434 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-114.cmd -datacodepage 4102 -l _BIC_B0000452000-114.log -stop_on_error

  sb1adm 14352578 24182820   1 02:55:54  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-106.cmd -datacodepage 4102 -l _BIC_B0000452000-106.log -stop_on_error

  sb1adm 14614768 24182820   0 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-97.cmd -datacodepage 4102 -l _BIC_B0000452000-97.log -stop_on_error

  sb1adm 15204588 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-111.cmd -datacodepage 4102 -l _BIC_B0000452000-111.log -stop_on_error

  sb1adm 16187644 24182820   0 02:55:55  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-116.cmd -datacodepage 4102 -l _BIC_B0000452000-116.log -stop_on_error

  sb1adm 16973862 24182820   3 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-100.cmd -datacodepage 4102 -l _BIC_B0000452000-100.log -stop_on_error

  sb1adm 17432622 24182820  13 02:55:56  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-115.cmd -datacodepage 4102 -l _BIC_B0000452000-115.log -stop_on_error

  sb1adm 17694774 24182820   0 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-95.cmd -datacodepage 4102 -l _BIC_B0000452000-95.log -stop_on_error

  sb1adm 18219062 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-118.cmd -datacodepage 4102 -l _BIC_B0000452000-118.log -stop_on_error

  sb1adm 18284732 24182820   7 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-102.cmd -datacodepage 4102 -l _BIC_B0000452000-102.log -stop_on_error

  sb1adm 18350136 24182820   0 02:55:55  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-113.cmd -datacodepage 4102 -l _BIC_B0000452000-113.log -stop_on_error

  sb1adm 18546756 24182820  27 02:55:54  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-107.cmd -datacodepage 4102 -l _BIC_B0000452000-107.log -stop_on_error

  sb1adm 19857532 24182820   3 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-98.cmd -datacodepage 4102 -l _BIC_B0000452000-98.log -stop_on_error

  sb1adm 19988616 24182820   7 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-99.cmd -datacodepage 4102 -l _BIC_B0000452000-99.log -stop_on_error

  sb1adm 20709510 24182820   3 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-96.cmd -datacodepage 4102 -l _BIC_B0000452000-96.log -stop_on_error

  sb1adm 21168272 24182820   0 02:55:56  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-110.cmd -datacodepage 4102 -l _BIC_B0000452000-110.log -stop_on_error

  sb1adm 22675464 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-119.cmd -datacodepage 4102 -l _BIC_B0000452000-119.log -stop_on_error

  sb1adm 23658742 24182820   0 02:55:56  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-108.cmd -datacodepage 4102 -l _BIC_B0000452000-108.log -stop_on_error

  sb1adm 23724272 24182820   6 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-103.cmd -datacodepage 4102 -l _BIC_B0000452000-103.log -stop_on_error

  sb1adm 23789806 24182820   2 02:55:50  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-91.cmd -datacodepage 4102 -l _BIC_B0000452000-91.log -stop_on_error

  sb1adm 24379406 24182820  14 02:55:56  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-104.cmd -datacodepage 4102 -l _BIC_B0000452000-104.log -stop_on_error

  sb1adm 24444938 24182820   0 02:55:54  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-117.cmd -datacodepage 4102 -l _BIC_B0000452000-117.log -stop_on_error

  sb1adm 24510472 24182820   3 02:55:53  pts/4  0:42 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-94.cmd -datacodepage 4102 -l _BIC_B0000452000-94.log -stop_on_error

  sb1adm 24576188 24182820   7 02:55:54  pts/4  0:43 /sapmnt/SB1/exe/R3load -e _BIC_B0000452000-101.cmd -datacodepage 4102 -l _BIC_B0000452000-101.log -stop_

 

  • Due to the table /BIC/B0000452000 was split into many packages according to WHERE condition with column "RECORD", so one index must be created on this column to improve performance for SELECT statement. You can see below, the index is being used and worked correctly.

db2 => select indname,colnames from syscat.indexes where tabname='/BIC/B0000452000'

INDNAME                                                                                                                          COLNAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/BIC/B0000452000~0                                                                                                               +REQUEST+DATAPAKID+RECORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
/BIC/B0000452000~IMG                                                                                                             +RECORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

  2 record(s) selected.

db2 =>

 

  • db2expln -database SB1 -f SQLStatement2.sql –terminal

Statement:

  SELECT "REQUEST" , "DATAPAKID" , "RECORD" , "INSP_LOT" , "INSP_CHAR"
          , "COUNTRY" , "/BIC/FARM" , "/BIC/SETTLE_DT" , "/BIC/AFL_DT"
          , "INSPTYPE" , "MASTERCHAR" , "/BIC/ZDAYDIFF" ,
          "/BIC/ROUTENR" , "/BIC/MODT_DT" , "/BIC/ZMODTDIFF"
  FROM SAPPB1."/BIC/B0000452000"
  WHERE ("RECORD" > 911)AND ("RECORD" <=921)


Section Code Page = 819

Estimated Cost = 94919512.000000
Estimated Cardinality = 6232849.000000

Access Table Name = SAPPB1./BIC/B0000452000  ID = 15,560
|  Index Scan:  Name = SAPPB1./BIC/B0000452000~IMG  ID = 2
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: RECORD (Ascending)
|  #Columns = 15
|  Skip Inserted Rows
|  Skip Deleted Rows
|  Avoid Locking Committed Data
|  Evaluate Predicates Before Locking for Key
|  #Key Columns = 1
|  |  Start Key: Exclusive Value
|  |  |  |  1: 911
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 921
|  Data Prefetch: Eligible 3795676
|  Index Prefetch: Eligible 3795676
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 15
Return Data Completion

After above optimization, I am sure the R3load should be benefit of it just like what I did before. But it is not the case, below is the analysis result.

I run the command  "db2 get snapshot for application agentid 5079 | grep -i rows" (only for 1 package) one time each second to observe the result

db2 get snapshot for application agentid 5079 | grep -i rows

Internal rows deleted                      = 0

Internal rows inserted                     = 0

Internal rows updated                      = 0

Rows deleted                               = 0

Rows inserted                              = 0

Rows updated                               = 0

Rows selected                              = 5347717

Rows read                                  = 5347710

Rows written                               = 0

Rows read                                  = 5298960

Rows written                               = 0

Rows deleted                               = 0

Rows updated                               = 0

Rows inserted                              = 0

Rows fetched                               = 5298827

arlssap03:sb1adm 303> db2 get snapshot for application agentid 5079 | grep -i rows

Internal rows deleted                      = 0

Internal rows inserted                     = 0

Internal rows updated                      = 0

Rows deleted                               = 0

Rows inserted                              = 0

Rows updated                               = 0

Rows selected                              = 5348176

Rows read                                  = 5348169

Rows written                               = 0

Rows read                                  = 5299419

Rows written                               = 0

Rows deleted                               = 0

Rows updated                               = 0

Rows inserted                              = 0

Rows fetched                               = 5299321

arlssap03:sb1adm 304> db2 get snapshot for application agentid 5079 | grep -i rows

Internal rows deleted                      = 0

Internal rows inserted                     = 0

Internal rows updated                      = 0

Rows deleted                               = 0

Rows inserted                              = 0

Rows updated                               = 0

Rows selected                              = 5348479

Rows read                                  = 5348472

Rows written                               = 0

Rows read                                  = 5299722

Rows written                               = 0

Rows deleted                               = 0

Rows updated                               = 0

Rows inserted                              = 0

Rows fetched                               = 5299321

As you can see, the line "Rows selected" changed only a few hundreds each time which is too slow. For other tables, when I do this test, it is by tens of thousands of each!

Please have a look, if some optimization can be done on DB2 level and let me know if you have any question.

Br, Kevin

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Anyone ideas?

divyanshu_srivastava3
Active Contributor
0 Kudos

Hi Kevin,

Have you checked this note before starting the process ?

Note 857081 - Unicode conversion: downtime estimate

What about your hardware resources and OS ?

Regards,

Divyanshu