< Previous | Next >

Exploring database administration tasks

In this lesson, you will work in the Data Source Explorer to explore the basic database administration tasks that are available in the product.

You work in the Data Source Explorer to create and alter data objects, manage privileges, and modify data. In a typical development scenario, you perform these types of tasks on test databases.

First, create a new table using the Data Object editor.

  1. In the Data Source Explorer, expand the SAMPLE connection, the database node, and the Schemas folder.
  2. Expand the schema that corresponds to the user ID that you used to connect to the database.
  3. Right-click the Tables folder, and select Create > Table. The Data Object editor opens.
    Screen capture showing the Create Table menu, as described.
  4. Use the properties pages in the Data Object Properties section to modify the properties of the new table.
    Tip: If you wanted to create this table as a copy of an existing table, you would click Import Definition to use the properties of the existing table.
    • Type a name for the table on the General page.
    • Add columns for the table on the Columns page.
    • Optional: Modify privileges for the table on the Privileges page. You can add and alter privileges and specify whether privileges can be granted to other authorization IDs.
      Tip: You can also modify role privileges by right-clicking a role in the Data Source Explorer and selecting Alter, or by right-clicking the Roles folder and selecting Create.
  5. Click Preview DDL to see the generated DDL script in the DDL section of the editor.
    Tip: If you wanted to save the DDL script and run it later, you could click Open with SQL editor and save the script from the editor to the data development project that you created in the last lesson.
  6. Click Run DDL to run the DDL script and create the table.

    The script is run on the target database and the results, including any errors, are displayed in the SQL Results view. The new table is displayed in the Data Source Explorer under the schema in which you created it.

    The new table

    Tip: You can save and print the information in the SQL Results view.
  7. Optional: Generate a DDL script to create a copy of this table:
    1. Close the Data Object Editor so that NEWTABLE is no longer being edited.
    2. Right-click the new table in the Data Source Explorer, and select Generate DDL.
    3. On the Options and Objects pages of the wizard, select DDL options and objects that you want to include in the script. You can update statistics before you generate the script, include DROP statements, or include privilege information in the script.
    4. On the Save and Run DDL page of the wizard, preview the generated script, specify a folder on the file system in which to store the generated script, and provide a name for the script. You can also choose to run the script or open it for editing. In this case, just save the script.
    5. Click Finish. The DDL script is generated and saved in the folder that you specified. You can run the script later to generate the copy of the table.

      Next, modify another table. You use the Data Object editor to complete this task. However, first you will analyze the impact of your changes.

  8. Right-click the EMPLOYEE table in the Data Source Explorer, and select Analyze Impact.
  9. In the window that opens, specify Both to return objects that are both impacted by and dependent on the table, include contained and recursive object in the analysis, and click OK.

    Results are displayed in the Model Report view. You can navigate to an impacted object in the Data Source Explorer by double-clicking it in the Model Report view. Use this feature to understand the impact of changes to a data object before you commit them.

    This screen capture shows an example of an impact analysis report. Your report will look different depending on whether other objects have been created that have dependencies on the EMPLOYEE table.

    Screen capture showing an example impact analysis report in the Model Report view.

  10. Optional: Run and view statistics for the EMPLOYEE table to improve performance:
    1. Right-click the EMPLOYEE table and select Update Statistics. Results of the RUNSTATS action are displayed in the SQL Results view.
    2. Highlight the EMPLOYEE table and open the Properties view.
    3. Click the Statistics tab to view statistics for the table.
      Screen capture showing the Statistics page, as described.
    Now, use the editor again to modify the table.
  11. Right-click the EMPLOYEE table, and select Alter.
  12. Add two columns on the Columns page, click Preview DDL in the DDL section of the editor, and click Run DDL. The new columns are created and displayed in the Data Source Explorer under the EMPLOYEE table.
< Previous | Next >

Feedback