Generate ER diagram using schemacrawler for tables in DB2

[virajshetty7]

I had to get the relationship between the tables in a particular schema, by generating ER diagram, How do I do that? I tried giving the tables' name in .includeTables method, but it didn't work. Would really appreciate the help here
Have given .schemaaInfoLevel to minimum()

[sualeh]

Please take a look at the examples in https://github.com/schemacrawler/SchemaCrawler/tree/master/schemacrawler-examplecode/src/main/java/com/example

[virajshetty7]

Thanks for this @sualeh
I'm trying to get the ER diagram, but I'm getting an error saying that "Cannot generate diagram in <[png] Portable Graphics format> output format". I've tried with multiple output formats(PNG, JPG, HTMLX... etc.) but still getting the same error, I've looked thoroughly into the code, still unable to find the issue. Would really appreciate your help.

Thank you

[sualeh]

Please send a complete log. See the instructions on the consulting page.

[virajshetty7]

I'm writing a script in Scala language and have made the connection to DB2. I'm passing a particular schema and a table in that, to produce the ER diagram

val limitOptionsBuilder = LimitOptionsBuilder.builder
           .includeSchemas(new RegularExpressionInclusionRule("schema_name"))
           .includeTables((tableName: String) => !tableName.contains("table_name_thats_in_the_above_scema"))

val loadOptionsBuilder = LoadOptionsBuilder.builder.withSchemaInfoLevel(SchemaInfoLevelBuilder.standard())

val options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions
                  .withLimitOptions(limitOptionsBuilder.toOptions())
                  .withLoadOptions(loadOptionsBuilder.toOptions())


var catalog :Catalog = null
catalog = SchemaCrawlerUtility.getCatalog(connection, options)

And for the output code of ER diagram, below is the code:

val command = "schema"
val diagramOptionsBuilder = builder
val diagramOptions = diagramOptionsBuilder.toOptions()
diagramOptionsBuilder.sortTables(true)
diagramOptionsBuilder.noInfo(diagramOptions.isNoInfo)
val additionalConfig = new Config()
additionalConfig.merge(diagramOptionsBuilder.toConfig)
additionalConfig.put("schemacrawler.format.hide_weakassociation_names", "true")
val executable = new SchemaCrawlerExecutable(command)
executable.setSchemaCrawlerOptions(options)
executable.setAdditionalConfiguration(additionalConfig)
executable.setConnection(connection)
executable.setCatalog(catalog)
val testMethodName = "new_png_test_method"
saveDiagram(executable, testMethodName)

def saveDiagram(executable: SchemaCrawlerExecutable, testMethodName: String): Unit = {
  val directory = "local_file_path"
  val testDiagramFile = Paths.get(directory).toAbsolutePath.normalize
  println(testMethodName)
  val oldOutputOptions = executable.getOutputOptions
  val outputOptions = OutputOptionsBuilder.builder
.fromOptions(oldOutputOptions)
.withOutputFile(testDiagramFile)
.withOutputFormat(DiagramOutputFormat.png).toOptions()
  executable.setOutputOptions(outputOptions)
  executable.execute()
}

The exception message is: "Cannot generate diagram in <[png] Portable Graphics format> output format"

[sualeh]

Please install Graphviz. There is documentation on the SchemaCrawler website that I think you should read very carefully.

[virajshetty7]

Thank you for this @sualeh it worked. I want to store the output in a cloud object storage path (COS Bucket). How can I do that?

[sualeh]

@virajshetty7 - take a look at how to set an output writer for SchemaCrawler. Then set the output options in SchemaCrawlerOptions using SchemaCrawlerOptionsBuilder

[virajshetty7]

Thanks for this @sualeh
I'm using this SchemaCrawlerOptionsBuilder with options, I'm new to this and I'm not sure how to add a writer option in here

val options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions
        .withLimitOptions(limitOptionsBuilder.toOptions())
        .withLoadOptions(loadOptionsBuilder.toOptions())

Output option builder:

val outputOptions = OutputOptionsBuilder.builder
            .fromOptions(oldOutputOptions)
            .withOutputFile(testDiagramFile)
            .withOutputFormat(DiagramOutputFormat.png).toOptions()

Here testDiagramFile is the local file path, now I need to write the output(ER Diagram) directly to the COS bucket.
I tried looking into withOutputWriter, but not sure how to pass a writer parameter.

[sualeh]

@virajshetty7 - I suggest that you refer to a Scala programming guide. This is outside of what I can help you with.

[virajshetty7]

Thank you @sualeh