Example to create a Windows Batch script to run DB2 backup commands

Contents of d:\db2_backup.bat

db2cmd /c /w "db2 backup database sampledb online to "d:\backup" include logs without prompting"

And schedule the script using Windows Scheduler.

DB2 Online backup and restoration example

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to sampledb

C:\Program Files\IBM\SQLLIB\BIN>db2 get db config |findstr LOGRETAIN
 Log retain for recovery enabled             (LOGRETAIN) = OFF

C:\Program Files\IBM\SQLLIB\BIN>db2 get db config |findstr LOGARCHMETH1
 First log archive method                 (LOGARCHMETH1) = OFF


C:\Program Files\IBM\SQLLIB\BIN>db2 update db config using logretain "recovery"

C:\Program Files\IBM\SQLLIB\BIN>db2 update db config using logarchmeth1 "disk:c:\DB2\Logs"

C:\Program Files\IBM\SQLLIB\BIN>db2stop force

C:\Program Files\IBM\SQLLIB\BIN>db2start


C:\Program Files\IBM\SQLLIB\BIN>db2 backup database sampledb online to "d:\backup" include logs without prompting

Backup successful. The timestamp for this backup image is : 20150803223509

C:\Program Files\IBM\SQLLIB\BIN>db2 force application all

C:\Program Files\IBM\SQLLIB\BIN>db2 restore database sampledb from "d:\backup" taken at 20150803223509

C:\Program Files\IBM\SQLLIB\BIN>db2 rollforward database sampledb to end of backup and complete

                                 Rollforward Status

 Input database alias                   = sampledb
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000004.LOG - S0000005.LOG
 Last committed transaction             = 2015-08-03-14.35.14.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

The domain join cannot be completed because the SID of the domain you attempted to join was identical to the SID of this machine

---------------------------
Computer Name/Domain Changes
---------------------------
The following error occurred attempting to join the domain "dbaglobe.com":

The domain join cannot be completed because the SID of the domain you attempted to join was identical to the SID of this machine. This is a symptom of an improperly cloned operating system install.  You should run sysprep on this machine in order to generate a new machine SID. Please see http://go.microsoft.com/fwlink/?LinkId=168895 for more information.
---------------------------
OK  
---------------------------

 

How to fix:

c:\Windows>cd c:\windows\system32\sysprep

c:\Windows\System32\Sysprep>dir
Volume in drive C has no label.
Volume Serial Number is 4EBE-B271

Directory of c:\Windows\System32\Sysprep

03/21/2014  12:18 PM    <DIR>          .
03/21/2014  12:18 PM    <DIR>          ..
08/01/2015  06:47 PM    <DIR>          ActionFiles
03/21/2014  11:09 AM    <DIR>          en-US
08/01/2015  05:33 PM    <DIR>          Panther
03/21/2014  11:49 AM           432,640 sysprep.exe
08/22/2013  04:02 AM           991,744 unbcl.dll
               2 File(s)      1,424,384 bytes
               5 Dir(s)  40,658,804,736 bytes free

c:\Windows\System32\Sysprep>Sysprep /generalize /shutdown /oobe

 

After sysprep generalization, it works per normal:

Reference: https://technet.microsoft.com/en-us/library/hh824938.aspx

Sample on optimizing SQL Server queries using indexed view

 

Sample DW query:

SELECT d1.EnglishEducation AS [Customer Education Level], CASE    WHEN d1.HouseOwnerFlag = 0 THEN 'No'    ELSE 'Yes' END AS [House Owner], COUNT(*) AS [Internet Order Quantity], ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount] FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1 INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1    on f1.CustomerKey=d1.CustomerKey INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] d2    on d1.GeographyKey=d2.GeographyKey WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007 AND  d2.EnglishCountryRegionName<>'United States' GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag GO

Create indexed View:

if object_id('VW_EnglishEducation') is not null     drop view VW_EnglishEducation GO create view VW_EnglishEducation with schemabinding as SELECT     d1.EnglishEducation,     d1.HouseOwnerFlag,     f1.SalesAmount,     f1.SalesOrderNumber,     f1.SalesOrderLineNumber FROM [dbo].[FactInternetSales] f1 INNER JOIN [dbo].[DimCustomer] d1    on f1.CustomerKey=d1.CustomerKey INNER JOIN [dbo].[DimGeography] d2    on d1.GeographyKey=d2.GeographyKey WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007 AND  d2.EnglishCountryRegionName<>'United States' GO create unique clustered index VW_EnglishEducationon_Idx on VW_EnglishEducation (SalesOrderNumber,SalesOrderLineNumber) GO

Limitations and Restrictions

• The definition of an indexed view must be deterministic.

• The user that executes CREATE INDEX must be the owner of the view.

• When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

• Tables must be referenced by two-part names, schema.tablename in the view definition.

• User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

• Any user-defined functions referenced in the view must be referenced by two-part names, schema.function.

• The data access property of a user-defined function must be NO SQL, and external access property must be NO.

• Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

• The view must be created by using the WITH SCHEMABINDING option.

• The view must reference only base tables that are in the same database as the view. The view cannot reference other views.

• If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

• The SELECT statement in the view definition must not contain the following Transact-SQL elements:

  COUNT	ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)	OUTER joins (LEFT, RIGHT, or FULL)
  Derived table (defined by specifying a SELECT statement in the FROM clause)	Self-joins	Specifying columns by using SELECT * or SELECT table_name.*
  DISTINCT	STDEV, STDEVP, VAR, VARP, or AVG	Common table expression (CTE)
  float*, text, ntext, image, XML, or filestream columns	Subquery	OVER clause, which includes ranking or aggregate window functions
  Full-text predicates (CONTAIN, FREETEXT)	SUM function that references a nullable expression	ORDER BY
  CLR user-defined aggregate function	TOP	CUBE, ROLLUP, or GROUPING SETS operators
  MIN, MAX	UNION, EXCEPT, or INTERSECT operators	TABLESAMPLE
  Table variables	OUTER APPLY or CROSS APPLY	PIVOT, UNPIVOT
  Sparse column sets	Inline or multi-statement table-valued functions	OFFSET
  CHECKSUM_AGG

  *The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

What is about the warning icon:

Database hang or may fail to OPEN in 12c IBM AIX - ORA-742

Oracle 12c introduces a new default feature of using multiple LGWRs which may lead to DEADLOCK / Database Hang or ORA-742 "Log read detects lost write" or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery on IBM AIX.

DEADLOCK or ORA-742 "Log read detects lost write" or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery.

PMON may terminate the instance while extensive block recovery is being performed.

Please disable the new feature of multiple LGWR slave processes by proactively setting _use_single_log_writer=true.  This is a temporary recommendation for IBM AIX installations until a formal fix is identified for this problem.

Setting _use_single_log_writer = true is a safe workaround; it is the behavior before 12c where multiple LGWR slave groups were not available. 

ALTER SYSTEM SET "_use_single_log_writer"=TRUE SID='*' SCOPE=SPFILE;
-- Restart the database or all instances of the RAC database

Note that while _use_single_log_writer=true is not set, then error ORA-600 [kcrfrgv_nextlwn_scn] might be produced avoiding the database to OPEN.  Once the problem is introduced, _use_single_log_writer=true may or may not fix it. _use_single_log_writer = true prevents inconsistencies in the redo log to be introduced which causes that error. 

If the parameter does not help, because the problem was already introduced when _use_single_log_writer=true had not been proactively set, then Point in Time Recovery (PITR) or Flashback Database are the options to recover from this situation.  

Oracle FailSafe Security Setup Tool Must be Run With Administrative Privileges

If a user is not logged into an account with administrative privileges and they start the Oracle Services for MSCS Security Setup utility (FsSvrSec.bat) from the Windows Start menu, the utility executes and reports that it ran sucessfully, even though it did not actually succeed in changing the account or password for the OracleMSCSServices service. The Windows Application event log shows the following events (note that error 5 is ERROR_ACCESS_DENIED, "Access is denied"):

Failed to open Service Control Manager with error: 5
Unable to set the user account for OracleMSCSServices service.
Unable to open cluster on local node.
Failed to open cluster with error 0

Failed to register Oracle Services for MSCS server with error: 10007.
Failed to create NT registry key AppID\{239D150B-FA41-11D1-BF40-00805FE9145B} with error: 5
Unable to set RunAs for OracleMSCSServices DCOM component.

To successfully run the tool, it is necessary to login to an account that has administrative privileges, or the FsSvrSec.bat file must be started from an MS-DOS command prompt that has been started with the Run as administrator option selected.

https://docs.oracle.com/cd/E16161_01/doc.342/e14976/toc.htm#BAJIIIAI

MySQL innodb hotbackup (mysqlbackup) with table filtering

C:\Program Files\MySQL\MySQL Enterprise Backup 3.12>mysqlbackup.exe -u root -p --include-tables="^sakila.(actor|address)$" --with-timestamp --backup-dir=c:\mysql\backup backup

MySQL Enterprise Backup version 3.12.0 Windows-6.0-x86 [10.03.2015 ]
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup.exe -u root -p --include-tables=^sakila.(actor|address)
        --with-timestamp --backup-dir=c:\mysql\backup backup

 mysqlbackup: INFO:
Enter password: ********
 mysqlbackup: INFO: MySQL server version is '5.6.25-enterprise-commercial-advanced-log'.
 mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup' run mysqlbackup
           prints "mysqlbackup completed OK!".

150706 22:39:31 mysqlbackup: INFO: MEB logfile created at c:\mysql\backup\2015-07-06_22-39-31\meta\MEB_2015-07-06.22-39-31_backup.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_data_home_dir =
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_data_home_dir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 14361935712403634

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150706 22:39:33 mysqlbackup: INFO: Full Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
150706 22:39:33 mysqlbackup: INFO: System tablespace file format is Antelope.
150706 22:39:33 mysqlbackup: INFO: Starting to copy all innodb files...
150706 22:39:33 mysqlbackup: INFO: Found checkpoint at lsn 8424992.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\ibdata1 (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Starting log scan from lsn 8424960.
150706 22:39:33 mysqlbackup: INFO: Copying log...
150706 22:39:33 mysqlbackup: INFO: Log copied, lsn 8424992.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\sakila\actor.ibd (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\sakila\address.ibd (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Completing the copy of innodb files.
150706 22:39:33 mysqlbackup: INFO: Starting to copy Binlog files...
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000001.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000003.
150706 22:39:33 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
150706 22:39:33 mysqlbackup: INFO: Starting to lock all the tables...
150706 22:39:33 mysqlbackup: INFO: All tables are locked and flushed to disk
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000004.
150706 22:39:34 mysqlbackup: INFO: Completed the copy of binlog files...
150706 22:39:34 mysqlbackup: INFO: Opening backup source directory 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\'
150706 22:39:34 mysqlbackup: INFO: Starting to backup all non-innodb files in
        subdirectories of 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\'
150706 22:39:34 mysqlbackup: INFO: Copying the database directory 'sakila'
150706 22:39:34 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150706 22:39:35 mysqlbackup: INFO: A copied database page was modified at 8424992.
          (This is the highest lsn found on page)
          Scanned log up to lsn 8424992.
          Was able to parse the log up to lsn 8424992.
          Maximum page number for a log record 0
150706 22:39:35 mysqlbackup: INFO: All tables unlocked
150706 22:39:35 mysqlbackup: INFO: All MySQL tables were locked for 1.783 seconds.
150706 22:39:35 mysqlbackup: INFO: Reading all global variables from the server.
150706 22:39:35 mysqlbackup: INFO: Completed reading of all global variables from the server.
150706 22:39:35 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in c:\mysql\backup\2015-07-06_22-39-31
150706 22:39:35 mysqlbackup: INFO: Full Backup operation completed successfully.
150706 22:39:35 mysqlbackup: INFO: Backup created in directory 'c:\mysql\backup\2015-07-06_22-39-31'
150706 22:39:35 mysqlbackup: INFO: MySQL binlog position: filename VMMDB01-bin.000004, position 120

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 8424960
   End LSN                    : 8424992
-------------------------------------------------------------

mysqlbackup completed OK!

## Before restoration, shutdown the MySQL instance.

C:\Program Files\MySQL\MySQL Enterprise Backup 3.12>mysqlbackup.exe --defaults-file="C:\MySQL\Backup\2015-07-06_22-40-55\server-my.cnf" --backup-dir=c:\mysql\backup\2015-07-06_22-40-55 copy-back-and-apply-log

MySQL Enterprise Backup version 3.12.0 Windows-6.0-x86 [10.03.2015 ]

Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...

 mysqlbackup.exe

        --defaults-file=C:\MySQL\Backup\2015-07-06_22-40-55\server-my.cnf

        --backup-dir=c:\mysql\backup\2015-07-06_22-40-55

        copy-back-and-apply-log

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

           prints "mysqlbackup completed OK!".

150706 22:48:47 mysqlbackup: INFO: MEB logfile created at c:\mysql\backup\2015-07-06_22-40-55\meta\MEB_2015-07-06.22-48-47_copy_back_dir_to_datadir.log

--------------------------------------------------------------------

                       Server Repository Options:

--------------------------------------------------------------------

  datadir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\

  innodb_data_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\

  innodb_data_file_path = ibdata1:12M:autoextend

  innodb_log_group_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\.\

  innodb_log_files_in_group = 2

  innodb_log_file_size = 50331648

  innodb_page_size = 16384

  innodb_checksum_algorithm = crc32

  innodb_undo_directory = C:\ProgramData\MySQL\MySQL Server 5.6\Data\

  innodb_undo_tablespaces = 0

  innodb_undo_logs = 128

--------------------------------------------------------------------

                       Backup Config Options:

--------------------------------------------------------------------

  datadir = c:\mysql\backup\2015-07-06_22-40-55\datadir

  innodb_data_home_dir = c:\mysql\backup\2015-07-06_22-40-55\datadir

  innodb_data_file_path = ibdata1:12M:autoextend

  innodb_log_group_home_dir = c:\mysql\backup\2015-07-06_22-40-55\datadir

  innodb_log_files_in_group = 2

  innodb_log_file_size = 50331648

  innodb_page_size = 16384

  innodb_checksum_algorithm = crc32

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

150706 22:48:47 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads

                1 read-threads    1 write-threads

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\ibdata1.

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\sakila\actor.ibd.

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\sakila\address.ibd.

150706 22:48:47 mysqlbackup: INFO: Starting to copy Binlog files...

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000001.

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000003.

150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000004.

150706 22:48:48 mysqlbackup: INFO: Completed the copy of binlog files...

150706 22:48:48 mysqlbackup: INFO: Copying the database directory 'sakila'

150706 22:48:48 mysqlbackup: INFO: Completing the copy of all non-innodb files.

150706 22:48:48 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in C:\ProgramData\MySQL\MySQL Server 5.6\Data\

150706 22:48:48 mysqlbackup: INFO: Copy-back operation completed successfully.

 mysqlbackup: INFO: Creating 14 buffers each of size 65536.

150706 22:48:48 mysqlbackup: INFO: Apply-log operation starts with following threads

                1 read-threads    1 process-threads

 mysqlbackup: INFO: Using up to 100 MB of memory.

150706 22:48:48 mysqlbackup: INFO: ibbackup_logfile's creation parameters:

          start lsn 8424960, end lsn 8424992,

          start checkpoint 8424992.

 mysqlbackup: INFO: Backup was originally taken with the --include regexp option

InnoDB: Doing recovery: scanned up to log sequence number 8424992

 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 5

0 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648

 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648

150706 22:48:50 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

          lsn 8424992.

 mysqlbackup: INFO: Last MySQL binlog file position 0 120, file name VMMDB01-bin.000004:120

150706 22:48:50 mysqlbackup: INFO: The first data file is 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\ibdata1'

          and the new created log files are at 'C:/ProgramData/MySQL/MySQL Server 5.6/Data/./'

150706 22:48:50 mysqlbackup: INFO: Apply-log operation completed successfully.

150706 22:48:50 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!