M424 Preparing for the Adaptive Server Anywhere Developer Certification Exam

1
M424 Preparing for the Adaptive Server Anywhere
Developer Certification Exam

• Robert Waywell
• Senior Product Support Consultant
• iAnywhere Solutions
• rwaywell_at_ianywhere.com

2
Objectives

• Understand the purpose of the certification exam
• Be aware of the topics covered in the exam
• Be confident that you can study for and pass the
  exam
• This session will NOT reveal any actual questions
  that the student will encounter on the exam.
  However, the types of questions will be covered.

3
Topics

• Overview of the Certification Exam
• Study Resources
• Exam Topics

4
Topics

• Overview of Certification Exam
• Resources
• Exam Topics

5
Purpose of the Certification Exam

• Test a practical working knowledge of Adaptive
  Server Anywhere
• Does Test
• Knowledge of terminology
• Ability to read understand syntax
• Understanding of features and behavior
• Does Not Test
• Memorization of syntax
• Platform specific functionality

6
Where do I take the exams?

• Call 1-800-8-SYBASE choose Education menu or
• At any Prometric (800-407-3926) testing center
  or
• Visit http//www.2test.com

7
When do I know if I passed?

• Immediately. After taking the test, your score is
  immediately printed out and sent to Sybase.
• A passing grade is 39/56 questions ? 70

8
What if I fail?

• The student is allowed to fail any of the two
  tests two times. If the student fails any of the
  two tests for the second time, the exam may not
  be taken again for the period of three months.

9
What benefits do I get?

• Industry Recognition
• Use of Logo on business cards
• Official Certificate
• Differentiator for employers and promoters

10
What Does Sybase Certification Offer You?

• Increases your job advancement opportunities
• Provides a standard, tangible way of measuring
  your technical skills
• Provides you with a goal for self-improvement and
  self-development
• To design and implement real-world solutions,
  which will make you valuable to your clients and
  your team

11
Topics

• Overview of Certification Exam
• Resources
• Exam Topics

12
How does one prepare for an exam?

• Study Guide
• Customer Education
• Product Documentation
• Experience with products
• Practice Exams
• Available at this conference and through eShop
• Expect to spend 40 hours preparing

13
Study Guide

• Sybase Adaptive Server Anywhere Developer
  (Version 8.0) Frequently Asked Questions and
  Study Guide
• http//www.sybase.com/detail/1,6904,1019777,00.htm
  l

14
Customer Education

• Adaptive Server Anywhere Fundamentals - Managing
  your Mobile and Workgroup Data
• http//www.sybase.com/detail?id1013112
• Upgrading to SQL Anywhere Studio 8.0
• http//www.sybase.com/detail?id1018511
• Schedules available at
• http//www.sybase.com/education

15
Topics

• Overview of Certification Exam
• Resources
• Exam Topics

16
What Topics are Covered?

• Connecting to the Database
• Creating and Using Databases
• Database Objects
• Manipulating Data
• Transaction Processing
• Backup and Recovery
• Security

• Common Administration Tools
• Automating Tasks with Schedules and Events
• Programming Interfaces
• Performance and Tuning
• Stored Procedures and Triggers
• Accessing Remote Data
• Deployment Issues

17
Connecting to the Database

• Understand the most common connection parameters
• Connect to network servers across common networks
• Connect to a specific database in a multiple
  database environment
• Specify a connection that automatically starts a
  database
• Specify a connection that automatically starts
  the personal server and a database
• Specify server and database names
• Specify common network parameters
• Common troubleshooting

18
Sample Question

• An application needs to connect to the test.db
  database file on the network database server
  test_server. The test.db file is located in the
  c\data directory on the server machine. The
  userid dba, with password sql, is used. The
  network database server is already running,
  supporting the TCP/IP network protocol for
  network client connectivity with host IP address
  of 128.128.128.2, and the default TCPIP Port
  2638. The database server was started with the
  -gd all switch. Currently the asademo.db
  database is the only database being served by the
  test_server server.
• The "test_server" ODBC data source has been set
  up on the client machine, and contains the
  following option settings
• User ID dba
• Password sql
• Server name test_server
• TCP/IP selected on the Network page

19
 Sample Question (cont)

• Which connect string can be used by the client
  application to connect to the test.db database?
• A. "DSNtest_server"
• B. "DBNtestDSNtest_server"
• C. "DBFc\data\test.dbDSNtest_server"
• D. "UIDdbaPWDsqlDBFc\data\test.dbLINKSTCPI
  P"
• E. LINKSTCPIPHOST128.128.128.2Port2638 DB
  Fc\data\test.db"

20
 Sample Question (cont)

• Which connect string can be used by the client
  application to connect to the test.db database?
• A. "DSNtest_server"
• B. "DBNtestDSNtest_server"
• C. "DBFc\data\test.dbDSNtest_server"
• D. "UIDdbaPWDsqlDBFc\data\test.dbLINKSTCPI
  P"
• E. LINKSTCPIPHOST128.128.128.2Port2638 DB
  Fc\data\test.db"

21
Creating Using Databases

• Identify and explain the purpose of database
  files including the main database file (.db),
  transaction log, temporary files and database
  spaces
• Discuss any limitations on size, name, etc.
• Describe how to create and delete a database
• Examine the most common creation options
• Explain where system tables are stored and what
  kind of information they contain

22
Sample Question

• Which attribute of a database must be set when
  the database is created and cannot be done at a
  later time?
• A. encryption
• B. jConnect support
• C. maximum cache size
• D. the transaction log file location

23
Sample Question

• Which attribute of a database must be set when
  the database is created and cannot be done at a
  later time?
• A. encryption
• B. jConnect support
• C. maximum cache size
• D. the transaction log file location

24
Sample Question

• What is the purpose of a custom collation?
• A. to define a custom sort order for the database
• B. to define custom attributes for characters in
  the collation
• C. to allow the database server to support the
  UTF8 character set
• D. to define custom character set translation
  tables for the database

25
Sample Question

• What is the purpose of a custom collation?
• A. to define a custom sort order for the database
• B. to define custom attributes for characters in
  the collation
• C. to allow the database server to support the
  UTF8 character set
• D. to define custom character set translation
  tables for the database

26
Database Objects

• Review basic database design principles
• Create, alter, and drop tables, primary keys,
  foreign keys, and views
• Use column defaults
• Create database mechanisms that enforce entity
  and referential integrity

27
Sample Question

• Which statement about primary keys is TRUE?
• A. Primary keys are indexed automatically.
• B. All ASA tables are required to have primary
  keys.
• C. Composite primary key columns must be of the
  same datatype.
• D. Primary keys can be stored in a separate
  dbspace from the table.

28
Sample Question

• Which statement about primary keys is TRUE?
• A. Primary keys are indexed automatically.
• B. All ASA tables are required to have primary
  keys.
• C. Composite primary key columns must be of the
  same datatype.
• D. Primary keys can be stored in a separate
  dbspace from the table.

29
Sample Question

• Which statement is FALSE about global temporary
  tables?
• A. The data is stored in the main database file.
• B. Actions on the table are not logged to the
  transaction log.
• C. They remain in the database until they are
  explicitly deleted.
• D. Data in the table is only visible to the
  connection that inserted it.
• E. The data is automatically deleted when the
  connection is closed.

30
Sample Question

• Which statement is FALSE about global temporary
  tables?
• A. The data is stored in the main database file.
• B. Actions on the table are not logged to the
  transaction log.
• C. They remain in the database until they are
  explicitly deleted.
• D. Data in the table is only visible to the
  connection that inserted it.
• E. The data is automatically deleted when the
  connection is closed.

31
Manipulating Data

• Perform basic data manipulation through Sybase
  Central
• Review basic SQL statements
• Review the concept of joins
• Use DBISQL

32
Sample Question

• Refer to the following query
• SELECT R.a
• FROM R LEFT OUTER JOIN S
• WHERE S.c lt '2000-01-03'
• What is the equivalent SQL statement?
• A. SELECT R.a FROM R LEFT OUTER JOIN S ON S.c lt
  '2000-01-03'
• B. SELECT R.a FROM R,S WHERE S.c lt '2000-01-03'
• C. SELECT R.a FROM R,S ON S.c lt '2000-01-03'
• D. SELECT R.a FROM R INNER JOIN S WHERE S.c lt
  '2000-01-03'

33
Sample Question

• Refer to the following query
• SELECT R.a
• FROM R LEFT OUTER JOIN S
• WHERE S.c lt '2000-01-03'
• What is the equivalent SQL statement?
• A. SELECT R.a FROM R LEFT OUTER JOIN S ON S.c lt
  '2000-01-03'
• B. SELECT R.a FROM R,S WHERE S.c lt '2000-01-03'
• C. SELECT R.a FROM R,S ON S.c lt '2000-01-03'
• D. SELECT R.a FROM R INNER JOIN S WHERE S.c lt
  '2000-01-03'

34
Transaction Processing

• Define the meaning of a transaction
• Explain when transactions begin and end in ASA
• Identify the main issues with concurrency and
  consistency
• Describe ASA locking
• Describe ASA blocking
• Describe isolation levels, the locks they imply
  and the issues they resolve

35
Sample Question

• Adaptive Server Anywhere first achieves cursor
  stability at which isolation level?
• A. 0
• B. 1
• C. 2
• D. 3

36
Sample Question

• Adaptive Server Anywhere first achieves cursor
  stability at which isolation level?
• A. 0
• B. 1
• C. 2
• D. 3

37
Sample Question

• Which statement explicitly completes a
  transaction?
• A. CHECKPOINT
• B. INSERT
• C. ROLLBACK
• D. DELETE
• E. END TRANSACTION

38
Sample Question

• Which statement explicitly completes a
  transaction?
• A. CHECKPOINT
• B. INSERT
• C. ROLLBACK
• D. DELETE
• E. END TRANSACTION

39
Backup Recovery

• Describe the types of failure an ASA database can
  experience
• Describe how ASA recovers from system failures
• Describe the data loss associated with media
  failure
• Describe how to protect from specific file loss
  (database, transaction log, mirror log)

40
Sample Question

• Which step is not part of the systems automatic
  recovery process?
• A. Recover to the most recent checkpoint
• B. Apply changes from temporary files
• C. Rollback uncommitted transactions
• D. Apply changes made since the checkpoint

41
Sample Question

• Which step is not part of the systems automatic
  recovery process?
• A. Recover to the most recent checkpoint
• B. Apply changes from temporary files
• C. Rollback uncommitted transactions
• D. Apply changes made since the checkpoint

42
Sample Question

• The computer or operating system becomes
  temporarily unavailable while there are partially
  completed transactions, perhaps because of a
  power failure or operating system crash, or
  because the computer is inappropriately rebooted.
• This type of database failure is called
• A. media failure
• B. cache failure
• C. corruption failure
• D. system failure

43
Sample Question

• The computer or operating system becomes
  temporarily unavailable while there are partially
  completed transactions, perhaps because of a
  power failure or operating system crash, or
  because the computer is inappropriately rebooted.
• This type of database failure is called
• A. media failure
• B. cache failure
• C. corruption failure
• D. system failure

44
Security

• Define the areas where ASA provides security
• Create and revoke users and groups
• Identify the types of permissions supported by
  ASA
• Use GRANT and REVOKE to assign permissions
• Secure the database file using encryption
• Secure client/server communications using
  encryption
• Describe the tools available to audit activity
• Describe how to control the actions a server can
  perform

45
Sample Question

• Which two permissions do members of a group
  inherit from the group?
• A. DBA
• B. SELECT
• C. RESOURCE
• D. UPDATE
• E. OWNER

46
Sample Question

• Which two permissions do members of a group
  inherit from the group?
• A. DBA
• B. SELECT
• C. RESOURCE
• D. UPDATE
• E. OWNER

47
Sample Question

• Enabling auditing, by setting the option
  PUBLIC.AUDITING to ON, logs which TWO actions?
• A. All result sets from SELECT statements
• B. All permissions checks, including the object
  on which the permission was checked
• C. Automatically implements double-entry
  accounting
• D. The results of evaluating CHECK constraints
• E. All login attempts including the terminal id
• F. Warnings related to diminished system resources

48
Sample Question

• Enabling auditing, by setting the option
  PUBLIC.AUDITING to ON, logs which TWO actions?
• A. All result sets from SELECT statements
• B. All permissions checks, including the object
  on which the permission was checked
• C. Automatically implements double-entry
  accounting
• D. The results of evaluating CHECK constraints
• E. All login attempts including the terminal id
• F. Warnings related to diminished system resources

49
Common Administration Tools

• Unload database - and how to reload and rebuild
• Upgrade database
• Translate log file
• Change log file settings
• Stop server
• DBISQL
• Unload/load table

50
Sample Question

• The Transaction Log (dblog) utility is used to
  change attributes of a transaction log file.
  What attribute can be changed?
• A. You can increase the size of the transaction
  log.
• B. You can change the collating sequence of the
  transaction log.
• C. You can change the location of the transaction
  log.
• D. You can compact the transaction log.

51
Sample Question

• The Transaction Log (dblog) utility is used to
  change attributes of a transaction log file.
  What attribute can be changed?
• A. You can increase the size of the transaction
  log.
• B. You can change the collating sequence of the
  transaction log.
• C. You can change the location of the transaction
  log.
• D. You can compact the transaction log.

52
Automating Tasks with Schedules Events

• Describe the difference between a schedule and an
  event
• Create a schedule to perform a routine task such
  as a backup
• Describe the types of events that can be
  responded to
• Create an event that provides notification when
  some condition has occurred (for example, low
  disk space)
• Describe the limitations of schedules and events

53
Sample Question

• Refer to the following code fragment
• CREATE EVENT IncrementalBackup
• SCHEDULE
• START TIME 100 AM EVERY 24 HOURS
• HANDLER
• BEGIN
• BACKUP DATABASE DIRECTORY c\\backup
• TRANSACTION LOG ONLY
• TRANSACTION LOG RENAME MATCH
• END
• When will the first, scheduled, incremental
  backup take place?
• A. Immediately after creating the event if after
  100 AM
• B. Immediately after starting server if after
  100 AM
• C. Next day at 100 AM if created after 100 AM
  on the current day
• D. Next day at 100 AM if created before 100 AM
  on the current day

54
Sample Question

• Refer to the following code fragment
• CREATE EVENT IncrementalBackup
• SCHEDULE
• START TIME 100 AM EVERY 24 HOURS
• HANDLER
• BEGIN
• BACKUP DATABASE DIRECTORY c\\backup
• TRANSACTION LOG ONLY
• TRANSACTION LOG RENAME MATCH
• END
• When will the first, scheduled, incremental
  backup take place?
• A. Immediately after creating the event if after
  100 AM
• B. Immediately after starting server if after
  100 AM
• C. Next day at 100 AM if created after 100 AM
  on the current day
• D. Next day at 100 AM if created before 100 AM
  on the current day

55
Programming Interfaces

• Describe the programming interfaces for ASA
• Describe when each interface can be used
• Describe specific behavior changes in ASA for
  each interface

56
Sample Question

• Which three database interfaces are available
  through Adaptive Server Anywhere?
• A. SQL Distributed Management Objects (SQL-DMO)
• B. Open Database Connectivity (ODBC)
• C. Open Data Services (ODS)
• D. Embedded SQL (ESQL)
• E. Java Database Connectivity (JDBC)

57
Sample Question

• Which three database interfaces are available
  through Adaptive Server Anywhere?
• A. SQL Distributed Management Objects (SQL-DMO)
• B. Open Database Connectivity (ODBC)
• C. Open Data Services (ODS)
• D. Embedded SQL (ESQL)
• E. Java Database Connectivity (JDBC)

58
Sample Question

• Which statement is FALSE about a database cursor?
• A. A cursor keeps track of the current position
  in a result set.
• B. A cursor is opened.
• C. A cursor requires all rows to be returned to
  the client application.
• D. A cursor is declared before it is used.

59
Sample Question

• Which statement is FALSE about a database cursor?
• A. A cursor keeps track of the current position
  in a result set.
• B. A cursor is opened.
• C. A cursor requires all rows to be returned to
  the client application.
• D. A cursor is declared before it is used.

60
Performance Tuning

• Identify and discuss hardware and software
  factors affecting database performance
• Describe how keys and indexes affect database
  performance
• Describe the trade-offs involved in using indexes
  and derived data
• Describe the impact of stored procedures and
  triggers on database performance
• Describe how the query optimizer works
• Identify the type of information given in a query
  plan
• AM44 Adaptive Server Anywhere Internals
• M404 ASA Performance Analysis From Beginning to
  End

61
Sample Question

• Which is TRUE about database rows?
• A. Rows are always stored on a single database
  page.
• B. Rows are always stored in primary key order on
  the database pages.
• C. Rows do not reserve space for NULLs.
• D. Rows that are deleted allow the database file
  to automatically shrink.

62
Sample Question

• Which is TRUE about database rows?
• A. Rows are always stored on a single database
  page.
• B. Rows are always stored in primary key order on
  the database pages.
• C. Rows do not reserve space for NULLs.
• D. Rows that are deleted allow the database file
  to automatically shrink.

63
Sample Question

• Which is FALSE about the query optimizer?
• A. It is a cost based optimizer.
• B. It rewrites the query.
• C. It uses statistics.
• D. It always picks the best access plan.

64
Sample Question

• Which is FALSE about the query optimizer?
• A. It is a cost based optimizer.
• B. It rewrites the query.
• C. It uses statistics.
• D. It always picks the best access plan.

65
Stored Procedures Triggers

• Describe the capabilities of triggers and
  procedures
• Explain the basic programming constructs
  available
• Explain how to create, run and debug triggers and
  procedures

66
Sample Question

• What permissions does a trigger execute with?
• A. Permissions of the table owner
• B. Permissions of the user executing the
  statement that fires the trigger
• C. DBA
• D. Triggers do not require permissions to execute

67
Sample Question

• What permissions does a trigger execute with?
• A. Permissions of the table owner
• B. Permissions of the user executing the
  statement that fires the trigger
• C. DBA
• D. Triggers do not require permissions to execute

68
Sample Question

• Which statement is TRUE for row-level triggers?
• A. They can only be defined to execute BEFORE the
  triggering insert, update, or delete modifies
  each row.
• B. They can only be defined to execute AFTER the
  triggering insert, update, or delete modifies
  each row.
• C. They can be defined to execute DURING the
  triggering insert, update, or delete that
  modifies each row.
• D. They can be defined to execute BEFORE or AFTER
  each row modified by the triggering insert,
  update, or delete.
• E. They can be defined to execute BEFORE or AFTER
  each triggering insert, update, or delete
  statement.

69
Sample Question

• Which statement is TRUE for row-level triggers?
• A. They can only be defined to execute BEFORE the
  triggering insert, update, or delete modifies
  each row.
• B. They can only be defined to execute AFTER the
  triggering insert, update, or delete modifies
  each row.
• C. They can be defined to execute DURING the
  triggering insert, update, or delete that
  modifies each row.
• D. They can be defined to execute BEFORE or AFTER
  each row modified by the triggering insert,
  update, or delete.
• E. They can be defined to execute BEFORE or AFTER
  each triggering insert, update, or delete
  statement.

70
Accessing Remote Data

• Describe how to create and use proxy tables
• Discuss what proxy tables can and cannot do

71
Sample Question

• The term remote server refers to
• A. any database object handling TDS requests
• B. the network server (dbsrv8) in a client-server
  environment
• C. is specifically used to refer to the network
  server in a WAN environment
• D. a database object that defines how to connect
  from the current database server to another
  database server

72
Sample Question

• The term remote server refers to
• A. any database object handling TDS requests
• B. the network server (dbsrv8) in a client-server
  environment
• C. is specifically used to refer to the network
  server in a WAN environment
• D. a database object that defines how to connect
  from the current database server to another
  database server

73
Sample Question

• Which statement is TRUE when executing a
  statement that involves a proxy table
• A. The full contents of the proxy table are
  always retrieved into the local ASA database in
  order to process the statement.
• B. The full contents of the local ASA tables are
  always pushed in to the remote server in order to
  process the statement.
• C. As much of the original statement as possible
  is handed off to the remote server involved.
• D. The local ASA engine manipulates the remote
  file store directly and bypasses the remote
  database server.

74
Sample Question

• Which statement is TRUE when executing a
  statement that involves a proxy table
• A. The full contents of the proxy table are
  always retrieved into the local ASA database in
  order to process the statement.
• B. The full contents of the local ASA tables are
  always pushed in to the remote server in order to
  process the statement.
• C. As much of the original statement as possible
  is handed off to the remote server involved.
• D. The local ASA engine manipulates the remote
  file store directly and bypasses the remote
  database server.

75
Deployment Issues

• Explain the major issues in deploying an ASA
  database server, client and database
• Describe the major components that can be
  deployed.

76
Sample Question

• Which environment variable is normally used by
  Adaptive Server Anywhere tools and utilities to
  locate the directory where the network database
  server was installed?
• A. ASANY8
• B. SYBASE
• C. SQLANY80
• D. SQLLOCALE

77
Sample Question

• Which environment variable is normally used by
  Adaptive Server Anywhere tools and utilities to
  locate the directory where the network database
  server was installed?
• A. ASANY8
• B. SYBASE
• C. SQLANY80
• D. SQLLOCALE

78
Sample Question

• Which is NOT an ASA file naming convention?
• A. redo file extension .rdo
• B. database file extension .db
• C. transaction log extension .log
• D. transaction log mirror extension .mlg

79
Sample Question

• Which is NOT an ASA file naming convention?
• A. redo file extension .rdo
• B. database file extension .db
• C. transaction log extension .log
• D. transaction log mirror extension .mlg

80
Closing Thought

• The CSP test is one of the better certifications
  to obtain for a programmer. It is the
  responsibility and the duty for all who take it
  to keep the exact questions confidential. The CSP
  certification should be EARNED by all. Never
  reveal the contents to anyone. Should the
  questions leak out, and inexperienced programmers
  start obtaining this certification, it diminishes
  the value of the certification for all who have
  obtained it legitimately, through hard work and
  studying.
• Keep the faith and good luck!!!